Three criteria SUMIFS

Christopher Moss

Good afternoon!

I am trying my hand at getting some detailed information about hours put in by volunteer by location. 

What I am trying to pull is from the screen shot in the two attachments.

here am I messing up on this formula?

=SUMIFS({Time (in yellow)}, {Location (in green)}, {Date (in blue)}, AND Location2 (orange) AND(@cell >= DATE((2024,1,1), @cell <=date(2024,1,31})

 

 

KPH

Continued from above

3 Adding in dates to the SUMIFS

You need to follow the pattern of range to look in, thing to look for, range to look in, thing to look for. AND is assumed in all SUMIFS. The result will be where all the parts are true.

So, to add your requirement for starts on or after you would add this part in bold. Note the is only one parenthesis between DATE and 2024.

=SUMIFS({Time}, {Location}, [Location2]@row, {Date}, @cell >= DATE(2024, 1, 1))

And then to add an end date you need to repeat the pair:

=SUMIFS({Time}, {Location}, [Location2]@row, {Date}, @cell >= DATE(2024, 1, 1), {Date}, @cell <= DATE(2024, 1, 31))

So now this:

Gives us this:

4 And finally

I noticed a little "Month" column on your sheet. I would love to include that in the formula so that you can change that one cell with "January" in it and update all your formulas without doing anything to the start and end dates.

Let me know if you are interested.

KPH

Hi @Christopher Moss

There are a few issues with the formula. I'll build it slowly for you.

1 Summing hours based on location.

The thing to sum goes first, then the range to look for something in, then the thing to look for. Your formula was missing the thing to look for (the bit in bold):

=SUMIFS({Time}, {Location}, [Location2]@row)

2 Also note I changed the name of the cross sheet references.

Using parenthesis can cause problems. There are workarounds but as it looks like you added these to make our lives easier, I have removed them. I gave the cross sheet references the same names as the columns.

Here is my dummy volunteer sheet

Are here is the output with the SUMIFS in the Time By Location column

more to follow...

KPH

Continued from above

3 Adding in dates to the SUMIFS

You need to follow the pattern of range to look in, thing to look for, range to look in, thing to look for. AND is assumed in all SUMIFS. The result will be where all the parts are true.

So, to add your requirement for starts on or after you would add this part in bold. Note the is only one parenthesis between DATE and 2024.

=SUMIFS({Time}, {Location}, [Location2]@row, {Date}, @cell >= DATE(2024, 1, 1))

And then to add an end date you need to repeat the pair:

=SUMIFS({Time}, {Location}, [Location2]@row, {Date}, @cell >= DATE(2024, 1, 1), {Date}, @cell <= DATE(2024, 1, 31))

So now this:

Gives us this:

4 And finally

I noticed a little "Month" column on your sheet. I would love to include that in the formula so that you can change that one cell with "January" in it and update all your formulas without doing anything to the start and end dates.

Let me know if you are interested.

Christopher Moss

@KPH I would love to not have to update the formulas based on the start/end date of the month and just have it go off of the month and year.

My goal for this is to pull the data into a dashboard to present to the administration to show where my volunteers are being utilized and were we can reach out to for additional volunteer support.

KPH

OK then @Christopher Moss

Can we change the Month column to be a date format column and to contain the 1st day of the month you are interested in*. That way it will also include the year, as well as the month so you can keep using the same formula next year without pulling in the past January's data, and so that we can use the DATE function.

* We could use Month End which is more common term in reporting, and an easier formula as you will be working between the 1st and whatever this end date is. However, this is prone to error as many people don't know what the last day of the month is and if someone enters March 30th then any hours on March 31st will never be tracked.

Assuming Month Start is OK then your new formula (90% complete) is:

=SUMIFS({Time}, {Location}, [Location2]@row, {Date}, @cell >= [Month starting]$1, {Date}, @cell <= DATE(YEAR([Month starting]$1), MONTH([Month starting]$1) + 1, 1) - 1)

It does this:

And it does it because...

This part is bold is your month start date in row 1 and it replaces your start date

=SUMIFS({Time}, {Location}, [Location2]@row, {Date}, @cell >= [Month starting]$1, {Date}, @cell <= DATE(YEAR([Month starting]$1), MONTH([Month starting]$1) + 1, 1) - 1)

Then this part it bold figures out the end date

=SUMIFS({Time}, {Location}, [Location2]@row, {Date}, @cell >= [Month starting]$1, {Date}, @cell <= DATE(YEAR([Month starting]$1), MONTH([Month starting]$1) + 1, 1) - 1)

It does it by making up a date:

• The year of which it takes from the Month starting cell.
• The month it also takes from the Month starting cell, and it adds 1 to it, so for January 1st start this becomes February.
• The day is 1, i.e. Feb 1st.

It then subtracts one from this, to figure out the last day in January.

There is a slight complication in that you can't subtract 1 from the month of January to find December. 1-1 is not 12. So we add another little IF to return December 31st of the year in the Month starting column.

=SUMIFS({Time}, {Location}, [Location2]@row, {Date}, @cell >= [Month starting]$1, {Date}, @cell <= IF(MONTH([Month starting]$1) = 12, DATE(YEAR([Month starting]$1), 12, 31), DATE(YEAR([Month starting]$1), MONTH([Month starting]$1) + 1, 1) - 1))

Now you see why an end date in the column would be easier! If you want to so that, the formula is a simple:

=SUMIFS({Time}, {Location}, [Location2]@row, {Date}, @cell >= DATE(YEAR([Month ending]$1), MONTH([Month ending]$1), 1), {Date}, @cell <= [Month ending]$1)