I have to columns of numbers. The numbers in column "B" define those in column "A" by colour ( or color).
| number | colour |
|---|---|
| 2 | blue |
| 3 | red |
| 7 | green |
| 5 | blue |
I want to know the sum of the numbers in A where they are blue. The correct answer here is "7". So I must want something along the lines of A6=SUM A2..A5 WHERE B2..B5 is "blue". Or am I pointing in the wrong direction?!
=sumif(B,"blue", A)
if the formula is in column C
you could use:
=sumif(B,B2, A)
=sumif(B,"blue", A)
if the formula is in column C
you could use:
=sumif(B,B2, A)
David,
Yes, SUMIF will do the trick.
The blue sum calculation is:
=SUMIF(B,"blue", A)
Regards,
Jerry
SUMIF ... ?
David,
SUM IF and SUMFS are different in one important way. SUM can only check one column, whereas SUMIFS can check multiple columns to determine what rows are included in the Sum. For some reason I can't explain, the syntax is reversed between the two versions.
SUMIF(test-values, condition, sum-values)
SUMIFS(sum-values, test-values, condition, test-values…, condition…)
Note that in SUMIF, the SUM value range is the last argument, and in SUMIFS the SUM value range is the first argument.
You may use SUMIFS even if you have only one condition to test for, so you could just as well have used:
=SUMIFS(A, B,"blue")
Regards,
Jerry
Now I'm going to have to try and find out the difference between SUMIF and SUMIFS...
I seem to have stumbled on the correct answer by using SUMIFS. In my read spreadsheet I'm going to use
SUMIFS(G2:G21,F2:F21,"S")
(the 'colours' are in column F, I'm adding up column G where the colour is "S")
Add numbers in column IF other column contains x
