SUMIFS with criterion referencing 2 date cells

Andi Hawes · 01/31/20

Dear Forum,

i would like a SUMIFS formula to be able to count hours in my timesheet on certain dates. All my hours and dates sit in one sheet, id like a second sheet to then count hours in certain dates.

As i understand it, SUMIFS needs:'

'Andi Hawes Timesheet Range 2' as the range i want to sum up in a nother sheet

Andi Hawes Timesheet Range 1 is the range of datesin a nother sheet,

'[Column7]11' is my from date, in a formula as its going to be flexible in my current sheet,

'[Column8]11' is my 'to' date in my current sheet,

but when i build myself the formula, i get as far as:

=SUMIFS({Andi Hawes Timesheet Range 2}, {Andi Hawes Timesheet Range 1}, <=[Column7]11)

and the formula builder doesnt allow me to add a second criterion?!!? This formula works as it counts all my hours from whatever date i add into '[Column7]11', but id like to add an upper limit of 'to' with the second criterion, but im unable to????

Any ideas?

Genevieve P. · 02/03/20

Hi Andi,

When adding in a second criteria, even if it's referencing the same range as your first one, you need to add in the range again. (Click here for the Help Center article on SUMIFS)

For example, try:

=SUMIFS({Andi Hawes Timesheet Range 2}, {Andi Hawes Timesheet Range 1}, <=[Column7]11, {Andi Hawes Timesheet Range 1}, >=[Column8]11)

Keep in mind that <= means before a date, and >= means after.

Let me know if this works! If not, it would be helpful to see some screen captures of your sheets (but please block out any sensitive data).

Cheers!

Genevieve

SUMIFS with criterion referencing 2 date cells

Answers

Help Article Resources

Categories