Sumifs: Is not working with two criteria

[Jonathan King]

SUMIFS has given me a headache, since I can't get the formula below to work, though I'm probably just too close to the trees to see the issue.

SUMIF works fine on TT and AH alone, but when both are used with SUMIFS, only zeros are returned.

What am I missing? Any help will be highly appreciated!

=IFERROR(SUMIFS(Data!I:I,B23,Data!T:T,Data!AH:AH,"=1"),0)

I - A column of text with department names (formatted as text)
B23 - one of the department names (formatted as text)
AH - A column of 1's and 0's (formatted as number)

Thanks!

 

[Jonathan King]

The headache had me forget to post info on the T-column:

=IFERROR(SUMIFS(Data!I:I,B23,Data!T:T,Data!AH:AH,"=1"),0)

I - A column of department names (formatted as text)
B23 - one of the department names (formatted as text)
T - A column of cost values (formatted as currency)
AH - A column of 1's and 0's (formatted as number)

Thanks!

 

[FormR]

Hi, the sumrange (I'm guessing column T from your description) should be the first argument, followed by the criteria range and criteria.

So..

=SUMIFS(Data!T:T,Data!I:I,B23,Data!AH:AH,"=1")

 

[Jonathan King]

Thanks FormR! That did the trick! I tried switching the last two, but for some reason didn't try switch the first one (i.e., column I).

 

[Jonathan King]

The one issue remaining, is that the I-column to B-cell match isn't always working. B23 and the other B-cells are dept names in text, like column I, but it's almost like it's hit or miss with the matching the text. I've tried different things to ensure there's no spaces or other issues, but at times the two sets of text are not being matched.

Any ideas?

 

[FormR]

but at times the two sets of text are not being matched.

You could try to test and troubleshoot which one is not matching by copying a simple formula down an adjacent column in the "data" sheet (change Sheet1 for the sheet that contains your criteria cell).

=I1='Sheet1'!$B$23

And looking for the cells that return FALSE where you expect them to match.

 

[Jonathan King]

Thanks!

That's what I've doing, along with using CountIF to see how many B's are included in I, to see which one are missed.

The same issue is seen in other reports, where they are being used. But I can't see the issue, since it's just simple text like, horizontal and vertical.

Yes, horizontal and vertical are both coming back false using your check, even though they are in the referenced cells.

But CountIF finds them in column I.

Any ideas?

 

[FormR]

But CountIF finds them in column I.
Any ideas?

This doesn't make sense. If the COUNTIF() thinks they match then so would the SUMIFS() - are you sure it's not the value in column AH that is not exactly equal to the criteria?

Perhaps you could post a cutdown version of your workbook that demonstrates the problem to a file sharing site (like dropbox) and share the link here so we can take a look.

 

[Jonathan King]

No it doesn't make sense, that's why I'm having so much "fun." After a review, the sumif and countif are returning the same values; the results are different when using a cell value vs the actual text.

AH has nothing to do with this issue. A listing of dept names are being used in different reports, which are the B-values, and they are being matched to the dept names in the column I.

=I2='Jobs by Dept'!$B$17 - this returns a false
=COUNTIF( I:I,"vertical") - this returns a 9.
=COUNTIF(I:I,B$17) - this returns a 0.
B17 is vertical

The problem must be in some of the B-values, but the vertical doesn't have any leading spaces or anything else that looks like a problem.

Retyping in the B-values, doesn't seem to help; though I've had that work in the past.

It's just strange, since some B-value dept names are ok, while others are not. It's difficult to explain to a client during testing, to day the least.

I'll post a sample to use, when there's some time.

 

[FormR]

What do these return?

='Jobs by Dept'!$B$17="Vertical"
=Data!I2="Vertical"
=LEN(Data!I2)
=LEN('Jobs by Dept'!$B$17)

Is the value in B17 returned from a formula or is it static text?