CODEWITHSUNDEEP
vbaexcel
Dezzie
Dezzie

Reputation: 984

Excel not capturing SUMIFS where condition is Text/Boolean

I have a sample data set with TRUE/FALSE values:

COLA   | COLB  | COLC 
------ | ----- | -----
ABCD   | 1234  | TRUE  
BCDE   | 2345  | FALSE
ABCD   | 3456  | TRUE

The formula which I'm attempting is =SUMIFS(COLB,COLC,"TRUE",COLA,"ABCD"), which is returning 0 instead of 4690.

If I instead use Boolean denotation instead of Text =SUMIFS(COLB,COLC,TRUE,COLA,"ABCD"), it still returns a 0.

Oddly enough, if I go back to the cells in COLC and double click + enter on the values, the contents get centered and then the formula starts working correctly: 

COLA   | COLB  | COLC 
------ | ----- | -----
ABCD   | 1234  |  TRUE  
BCDE   | 2345  | FALSE
ABCD   | 3456  |  TRUE

However, I don't want to do this because the data is being imported via a macro and the sheet shouldn't be touched.

How can I fix the formula to capture the text as it is currently in COLC?

Upvotes: 2

Views: 5496

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60334

SUMIFS is interpreting the TRUE in the criteria argument as Boolean. Your data is being imported as a text string.

You can force SUMIFs to view TRUE as a string by adding a wild card:

=SUMIFS(COLB,COLA,"ABCD",COLC,"*TRUE")

Obviously, not ideal, as it will count any string that ends with TRUE, but should work given your example.

An alternative, not dependent on wild cards is:

=SUMPRODUCT((COLA="ABCD")*(COLC="TRUE")*COLB)

If you need to be able to count TRUE whether it might be BOOLEAN or TEXT, try one of these:

=SUM(SUMIFS(COLB,COLA,"ABCD",COLC,{"*TRUE",TRUE}))
=SUMPRODUCT((COLA="ABCD")*(COLC={"TRUE",TRUE})*COLB)

Upvotes: 5

Related Questions