13

I want a cell to give me the sums of the cells above, only when another column has values in it as well. For instance, I want B5 to sum up B1:B4, only when C1:C4 also have values, if a C cell doesn't have a value, then I want the corresponding B cell to be counted as a 0 during the sum. I looking up info on SumIf and FILTER but I don't know what to put in the criteria to make it accept any number, and how to report 0 when no number is available. Any help would be appreciated.

What I tried doing so far: =FILTER(B1:B4;C1:C4=value() and I get an error

user28790
  • 133
  • 1
  • 1
  • 4

2 Answers2

30

Have you tried using SUMIF like this:

=SUMIF(C1:C4, "<>", B1:B4)

?

It's working for me.

Jerry
  • 70,495
  • 13
  • 100
  • 144
  • yes! thanks a lot. would you mind explaining to me what the "<>" does? – user28790 Sep 28 '13 at 12:38
  • 2
    @user28790 You can click the tick outline to accept my answer :) The `<>` means not equal to and since there's nothing, it will mean 'not equal to blank'. – Jerry Sep 28 '13 at 12:44
2

Try:

=SUMIF(C1:C4; "<>"; B1:B4)

or also, as Jerry suggested:

=SUMIF(C1:C4, "<>", B1:B4)

Note that the semi-colon separator is supported in all locales, so it is generally preferred.

nibbana
  • 698
  • 1
  • 8
  • 21