Menu
I want a spreadsheet function that will produce a sum of all values in column B for when column A is equal to X and when it is equal to Y.
A B
X 10
Y 3
X 7
X 22
Y 4
Y 9
The output should look like the following (where 39 and 16 are the results of the formulas):
X 39 -> 10 + 7 + 22
Y 16 -> 3 + 4 + 9
373
If you want, you can apply the criteria to one range and sum the corresponding values in a different range. For example, the formula =SUMIF(B2:B5, "John", C2:C5) sums only the values in the range C2:C5, where the corresponding cells in the range B2:B5 equal "John."
Sum if cell contains text If you are looking for an Excel formula to find cells containing specific text and sum the corresponding values in another column, use the SUMIF function. Where A2:A10 are the text values to check and B2:B10 are the numbers to sum. To sum with multiple criteria, use the SUMIFS function.
If you need to sum numbers based on multiple criteria, you can use the SUMIFS function. The first range (D4:D11) are the cells to sum, called the "sum range". Criteria are supplied in pairs... (range / criteria).
To sum cells that match multiple criteria, you normally use the SUMIFS function. The problem is that, just like its single-criterion counterpart, SUMIFS doesn't support a multi-column sum range. To overcome this, we write a few SUMIFS, one per each column in the sum range: SUM(SUMIFS(…), SUMIFS(…), SUMIFS(…))
Something like this
X 10
Y 3
X 7
X 22
Y 4
Y 9
X "=SUMIF(A1:A6;A8;B1:B6)"
Y "=SUMIF(A1:A6;A9;B1:B6)"
use SUMIF(range, criteria, sum_range): (sum values between B2-B8, using value in A2-A8 as criterea, using the specified condition)
=SUMIF(A2:A8,"=X",B2:B8)
=SUMIF(A2:A8,"=Y",B2:B8)
You can use SUMPRODUCT to calculate the totals. For the "X" values:
=SUMPRODUCT((A1:A6="X")*(B1:B6))
for the "Y" values:
=SUMPRODUCT((A1:A6="Y")*(B1:B6))
Hope that helps,
Eric Melski
EDIT: Apparently you must use ARRAYFORMULA to use SUMPRODUCT in Google's spreadsheet. See for example http://www.google.com/support/forum/p/Google+Docs/thread?tid=13a3eb824446e891&hl=en
40
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
