Sumifs: Is not working with two criteria

Jonathan King

New Member
Joined
Dec 13, 2018
Messages
17
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. :eek:

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!
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Jonathan King

New Member
Joined
Dec 13, 2018
Messages
17
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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,465
Office Version
  1. 365
Platform
  1. Windows
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")
 
Last edited:

Jonathan King

New Member
Joined
Dec 13, 2018
Messages
17
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

New Member
Joined
Dec 13, 2018
Messages
17

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,465
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

Jonathan King

New Member
Joined
Dec 13, 2018
Messages
17

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,465
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 13, 2018
Messages
17
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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,465
Office Version
  1. 365
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,900
Messages
5,598,761
Members
414,258
Latest member
Dbarton0231

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top