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]

='Jobs by Dept'!$B$17="Vertical" - False - there's the problem
=Data!I2="Vertical" - True
=LEN(Data!I2) - 9
=LEN('Jobs by Dept'!$B$17) - 8

With Vertical, it has a trailing space.

But in checking another problem child, horizontal, both LENs return 10. I was expecting to see an 11 with the B-value.

The B-values are all static text. Though in another report they come in via a formula, referencing a static list, from another sheet.

Is there a way to better input the values or does the value need to be trimmed?

 

[FormR]

Though in another report they come in via a formula, referencing a static list, from another sheet.

Is this other report relevant to the current situation? Are they copied as pasted as values from it?

What does this return?

=TRIM('Jobs by Dept'!$B$17)="Vertical"

 

[Jonathan King]

Sorry for the delay, but sleep took over.

=TRIM('Jobs by Dept'!$B$17)="Vertical" is true.

It can't be trimmed within the sumif can it?

By resolving this issue, the other reports can be corrected.

 

[FormR]

It can't be trimmed within the sumif can it?

Hi, Yes - going back to the original SUMIFS() formula, you can try:

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

 

[Jonathan King]

Great, FormR! That works!

Thanks for saving me! It's highly appreciated, to say the least!