CODEWITHSUNDEEP
excelexcel-formula
KromeWing
KromeWing

Reputation: 60

SUMIFS returning ZERO

I'm running some tests in this worksheet and for some reason that I don't know, SUMIFS is not working:

=SUMIFS($AC:$AC,$AB:$AB,$BA$1 & "-" & BB$1 & "-" & $BA3,$AB:$AB,$BA$1 & "-" & BB$1 & "-" & $BA4)

formula view

I just don't get why it is not working. Just above I used a COUNTIF with similar cell references and it worked:

=COUNTIF($AA:$AA,$BA$1 & "-" & BB$1 & "-" & $BA4)

formula view

I also used a SUMIF in a cell below just to test and it also worked:

=SUMIF(AB:AB,BA1 & "-" & BB1 & "-" & BA2,AC:AC)

formula view

The values in AA and AB are the same, the only difference is that some cells in AA are merged.

Values in AC are not formatted as text.

I double checked searching for a character misplaced (like a "space") and there is nothing wrong.

Already tried to write the criteria instead of use the cell references and didn't work. Tried also to select one of the cells of AB as criteria and didn't work. The result is always 0.

I just can't find where I'm messing up. Could someone please enlighten me?

Upvotes: 1

Views: 13664

Answers (1)

Ulli Schmid
Ulli Schmid

Reputation: 1167

Let's disect what your formula does. In the cell you've highlighted in your screenshot, it evaluates to

=SUMIFS($AC:$AC,$AB:$AB,"TANKER-MANAUS-AT ANCHOR",$AB:$AB,"TANKER-MANAUS-EXPECTED")

so this formula will sum up over all those cells in column AC that meet all of the following criteria:

  • their neighboring cell in AB is "TANKER-MANAUS-AT ANCHOR"
  • their neighboring cell in AB is "TANKER-MANAUS-EXPECTED"

since both those criteria can never be met at the same time, the result will always be zero.

You probably want something like this:

=SUMIFS($AC:$AC,$AB:$AB,"TANKER-MANAUS-AT ANCHOR")+SUMIFS($AC:$AC,$AB:$AB,"TANKER-MANAUS-EXPECTED")

or

=SUMIFS($AC:$AC,$AB:$AB,$BA$1 & "-" & BB$1 & "-" & $BA3)+SUMIFS($AC:$AC,$AB:$AB,$BA$1 & "-" & BB$1 & "-" & $BA4)

Upvotes: 2

Related Questions