Excel : sum of values depending of other values

Question

What would be the equivalent Excel formula of this SQL query

SELECT SUM(a) FROM table WHERE b < 0;

A     B
----  ----
   1     0
   2    -1 
   3     4
   5    -3

>> 7

score 2 · Answer 1 · answered Jun 02 '10 at 21:56

2

Use the SUMIF function.

answered Jun 02 '10 at 21:56

Marcelo Cantos

That's what I tried, but if I'm not mistaken, the condition is only on one cell or an equality. `SUMIF(B1:B4;<0;A1:A4)` obviously doesn't work, but what to write before the `<`? – gregseth Jun 02 '10 at 22:01

score 0 · Accepted Answer · answered Jun 02 '10 at 22:01

0

=SUMIF(B:B,"<0",A:A)

answered Jun 02 '10 at 22:01

Paul Kearney - pk

score 0 · Answer 3 · answered Jun 02 '10 at 22:02

0

use SUMIFS(a1:a4,b1:b4,"<0") if values in A1:B4 range

answered Jun 02 '10 at 22:02

bill

score 0 · Answer 4 · answered Jun 02 '10 at 22:05

0

Given:

Row#    A    B

3       1    0
4       2   -1
5       3    4
6       5   -3

=SUMIF(B3:B6; "<0"; A3:A6)

answered Jun 02 '10 at 22:05

Leniel Maccaferri

4 Answers4