Google sheets sum cells if corresponding date is in range of a month

Question

I have a sheet in google spreadsheets with every business day in one calendar week and the working hours of the staff. Now I am supposed to create a sheet displaying the salary of the employees for the month.

Sheet "Calendar Week 01":

Person A

Date	Working hours
01.01.2022	8,0
02.01.2022	8,5
03.01.2022	8,0
...	...

Person B

Date	Working hours
01.01.2022	8,0
02.01.2022	8,5
03.01.2022	8,0
...	...

Sheet "Calendar Week 02":

Person A

Date	Working hours
08.01.2022	8,0
09.01.2022	8,5
10.01.2022	8,0
...	...

Person B

Date	Working hours
08.01.2022	8,0
09.01.2022	8,5
10.01.2022	8,0
...	...

Now I'm supposed to create a sheet like this:

Sheet "Salary"

Person A

Month	Salary
January 2022	'=30*(working hours)'
Feburary 2022	'=30*(working hours)'
March 2022	'=30*(working hours)'
...	...

Person B

Month	Salary
January 2022	'=30*(working hours)'
Feburary 2022	'=30*(working hours)'
March 2022	'=30*(working hours)'
...	...

Since the working hours are noted for every calendar week I can't just sum all the hours to calculate the whole month's salary, since some calendar weeks contain days of the next/previous month. I couldn't think of any IF-formula either.

I thought maybe it's possible to link the values of "working hours" to the "date" in the same row, so I could do something like "if the date is in month 01, add the number of working hours in the same row as the date value".

I hope someone can help me solve my problem!

Edit: I don't know why the tables aren't displayed correctly in my post. They are shown correctly in my post preview...

Answer 1

You want to aggregate worked hours by employee by month, and calculate salary. Worked hours are recorded vertically in weekly sheets, by employee.

The data layout is highly inefficient. The amount of duplication of data elements is extremely high. Alternate layout designs would reduce duplication but this answer assumes that the structure described in the question is not negotiable.

The answer consists of two steps:

• Build an sheet to consolidate weekly data sheets
• Build a Query for data analysis

Step#1: Consolidate weekly data sheets

A new sheet

The data for each week is contained in sheets named "Calender Week 01", "Calender Week 02", and so on.

• Create a new sheet. Name it, say, "Weekly_Data"

• Column A: Sheet Names

  • Cell A5: Enter "Calendar Week 01"
  • Select the range A5:A11
  • Drag the range down as many rows as required. The sheet name weekly number will automatically increment.

• Column B: Dates

  • Cell B5: Create a link to the first date in Sheet "Calender Week 01"
  • Cell B6: create a formula =B5+1
  • Copy Cell B6 down as many rows as required.

• Column C/D/E - Person A, B, C, etc

  • Cell C1: enter the row number for the first row of data for this person. For example, refer to "Calender Week 01": Person A|Saturday, 1 Jan = Row#3, Person B|Saturday, 1 Jan = Row#14
  • Cell C2: enter the row number for the last row of data for this person. For example, refer to "Calender Week 01": Person A|Friday, 7 Jan = Row#9, Person B|Friday, 7 Jan = Row#20

The formula

• Insert this formula in cell C5 =indirect((address(C$1,2,4,1,$A5))&":"&(address(C$2,2,4,1)))

• The formula generates data in seven contiguous cells (one week).

• Copy the formula - down

• Select the range C5:C11, drag the range down as many weeks as required.

• Copy the formula - across

• Select a range in Column B, and copy the formula across as many columns as required.

Sample
The "Weekly_Data" sheet should look something like this (alternating colors were used only to highlight successive weeks)

---

Step#2: Data analysis

• Create a new sheet, name it as you please.

• Person A:

  • Cell B1: Enter the name for Person A
  • Cell A1: Enter this formula =match(B1,Weekly_Data!$4:$4)
  • Cell A2: Enter the formula =query({" "," ","Index","Month";query({Weekly_Data!$B$5:$B,query(INDIRECT(address(5,$A1,4,1,"Weekly_Data")&":"&substitute(address(1,$A1,4),"1",""))),ArrayFormula(if(len(Weekly_Data!$B$5:$B),upper(text(datevalue(Weekly_Data!$B$5:$B),"mmm")),""))},"select Col1, Col2,(month(Col1)+1),Col3 where Col1 is not null label (month(Col1)+1) ''")},"select Col3, Col4, Sum(Col2), Sum(Col2)*30 group by Col3, Col4 label sum(Col2) 'Hours', Sum(Col2)*30 'Salary' format Sum(Col2)*30 '$#,##0'")

• Person B:

  • You have 2 options here:
    • leave a blank row between the Person A data and the Person B data. Then as more months are added to the data, you can insert new rows between Person A or Person B.
    • go straight to row 16 (which leaves space for the 12 months of data for Person A)
  • Enter the name in Column B
  • Enter the match formula in the adjacent cell in Column A
  • Copy the formula from Person A to the next row of Column A

• Repeat for person C, D, and so on

Sample-Option#1

---

Sample-Option#2

---

There are other options for calculating the number of hours and salary. For example, sumproduct and 'sumif'.