SUMIF Using criteria from the Sheet Summary

Matt Galindo

Hi,

I have a sheet that captures hours from a vendor. Hours and rework counts (number of times an issue goes into rework) are entered. I need to sum hours and rework counts for the previous month. I have a calculation in the sheet summary the takes the year and month and forms a YYYY-MM field. I am comparing this sheet summary field to a column in the sheet.

I keep getting an #UNPARSABLE error with the formula I am using. I have checked this against examples and documentation and I don't see where I'm off. Here is the formula for the rework count:

=SUMIF([Completion Date YYYY-MM]:[Completion Date YYYY-MM], [Completion Date YYYY-MM](@cell) =[Previous YYYY-MM]#, [Task Reopen Count]:[Task Reopen Count])

Here are some of the other fields that are used to create this formula:

previous month - =IF(MONTH(TODAY()) = 1, 12, (MONTH(TODAY()) - 1))

previous year - =IF(MONTH(TODAY()) = 1, (YEAR(TODAY()) - 1), (YEAR(TODAY())))

previous YYYY-MM - =[Previous YYYY]# + "-" + [Previous Month]#

Hope someone has some suggestions on where I've gone off the track.

Once this is figured out, I will use the same format for the hours for the previous month.

Thanks

KPH

Hi

SUMIF doesn't usually have an = in the middle. The syntax is

=SUMIF( range, criterion, [ sum_range ])

(in other words the place to look and thing to look for are separated by a comma not an =)

I'm not quite following what you're doing, but maybe this is what you need

=SUMIF([Completion Date YYYY-MM]@cell, [Previous YYYY-MM]#, [Task Reopen Count]:[Task Reopen Count])

That would sum all the Task Reopen counts where the Completion date in the cell matches the sheet summary Previous YYY-MM

If that doesn't do the trick, can you share a screen shot of your sheet (removing anything that shouldn't need publicly viewed)?

KPH

Great news! Glad to have helped.

If you can, please click “yes” next to Did this answer the question?  on the answer, so others can find the solution easily.

KPH

Hi

SUMIF doesn't usually have an = in the middle. The syntax is

=SUMIF( range, criterion, [ sum_range ])

(in other words the place to look and thing to look for are separated by a comma not an =)

I'm not quite following what you're doing, but maybe this is what you need

=SUMIF([Completion Date YYYY-MM]@cell, [Previous YYYY-MM]#, [Task Reopen Count]:[Task Reopen Count])

That would sum all the Task Reopen counts where the Completion date in the cell matches the sheet summary Previous YYY-MM

If that doesn't do the trick, can you share a screen shot of your sheet (removing anything that shouldn't need publicly viewed)?

KPH

Here is an illustration of SUMIF in case that makes more sense.

In this example:

This formula

=SUMIF([Look for]:[Look for], "1", [Sum these]:[Sum these])

would result in 6

Which is the total sum of the numbers in the "Sum these" column that have a "1" in the "Look for" column. 1+2+3=6

If I change this to

=SUMIF([Look for]:[Look for], "3", [Sum these]:[Sum these])

The answer is 13 in other words 6 + 7

Matt Galindo

Thank you! This worked beautifully. I was also able to apply it to AVERAGEIF.

KPH

Great news! Glad to have helped.

If you can, please click “yes” next to Did this answer the question?  on the answer, so others can find the solution easily.