SUMIFS additional criteria

[Budaholic]

Hi guys

I have a SUMIFS formula to extract number of hours worked from a specific start and end date within a months range. (A time sheet export)

=SUMIFS(D2121:D2191,C2121:C2191,">=01/05/2018",D2121:D2191,"<=31/05/2018")

Can anyone tell me how to add an additional criteria (payroll no from column A) to automatically select the cell ranges for columns C (date range) and D (hours).

I've been googling like a mad man this morning, i know the answer is out there but it's eluding me.

Thanks

 

[Aladin Akyurek]

=SUMIFS($D$2121:$D$2191,$A$2121:$A$2191,J1,$C$2121:$C$2191,">="&K1,$D$2121:$D$2191,"<"&EOMONTH(K1,0)+1)

where J1 is a prayroll no of interest and K1 = 2018-05-01.

 

[Budaholic]

Am I right in assuming I can change the D, A and C values to D:D etc to cover the entire field and it will only return the values if field A:A equals cell J1 ? I do not need the EOMonth check, payroll has to be submitted last working day of the month so this will be variable.

 

[Aladin Akyurek]

Am I right in assuming I can change the D, A and C values to D:D etc to cover the entire field and it will only return the values if field A:A equals cell J1 ? I do not need the EOMonth check, payroll has to be submitted last working day of the month so this will be variable.

Yes, you can.

 

[Budaholic]

Just tried it and formula is spot on, thank you so much for you help.

 

[Aladin Akyurek]

Just tried it and formula is spot on, thank you so much for you help.

You are welcome. Thanks for the update.