SUMIFS Returning Zero?

Lauren Y

I am trying to sum all hours that meet a certain criteria (match the lookup column/project ID).

My Formula:

=SUMIF({0 | Affiliate TESS HOURS}, [LOOKUP COLUMN]@row, {0 | Affiliate TESS Lookup})

{0 | Affiliate TESS HOURS} --> references "Entered Hours"

{0 | Affiliate TESS Lookup} --> References "Lookup Column"

Formula is in the last column

Source Sheet

I tried even to multiply "entered hours" by 1 to ensure "hours" is counted as a number. I referenced both of those two columns at one point and neither summed to anything other than 0.

I confirmed by COUNTIF that there are many rows that match the lookup column so that shouldnt be the issue?

Lauren Y

At one point, I also wanted the criteria for "trailing hours" only (in last month) but gave up on adding the second condition.

Paul Newcome

Try switching the ranges in your formula. The syntax for SUMIF has the range to sum as the last range, so the Hours range should be in the last bit.

To add multiple range/criteria sets, you will need to switch over to a SUMIFS which does have a different syntax than SUMIF.

=SUMIFS(range_to_sum, 1st_criteria_range, 1st_criteria, 2nd_criteria_range, 2nd_criteria, ..........................)

Lauren Y

I switched it to this and its still giving me zero hours:

=SUMIF({0 | Affiliate TESS TO}, TO@row, {0 | Affiliate TESS HOURS})

(the match is the TO)

Lauren Y

Is it that there should be a match criteria for the TO@row? I want it to be whatever matches {0 | Affiliate TESS TO}

Lauren Y

And yes, that is it. Needed an = sign for the criteria.

Here is the formula that worked:

=SUMIF({0 | TESS Affiliate DSC TO}, =[ DSC TO]@row, {0 | Affiliate TESS Entered Hours})

Paul Newcome

It looks like you're not getting a match on the TO portion. Are you able to provide a screenshot showing the sheet with the formula alongside the source data where there should be a match that generates a sum?