Description

This article is from the Spreadsheets FAQ, by Russell Schulz casfaq@locutus.ofB.ORG with numerous contributions by others.

10.18.1 How do I sum up or count column A when corresponding cells in column B match some condition? (spreadsheets: Excel)

use an array formula; in MS-Windows, you enter an array formula
with Ctrl+Shift+Enter. on a Mac, use Command+Shift+Enter (I think).
Excel will add {braces} to the display to remind you it's an array.

SUMIF() and COUNTIF() functions in recent versions of Excel can
be simpler, but reduce portability, and can't handle the more
complex cases.

examples: data is in rows 10:50

- count entries in column B that are >10
=SUM(IF(B10:B50>10,1,0))

- sum entries in column B that are >10
=SUM(IF(B10:B50>10,B10:B50,0))

- average entries in column B that are >10
=SUM(IF(B10:B50>10,B10:B50,0)) / SUM(IF(B10:B50>10,1,0))

- sum column A when column B is >10
=SUM(IF(B10:B50>10,1,0)*A10:A50)

- sum column A * column B when column C is "blue"
=SUM(IF(C10:C50="blue",1,0)*A10:A50*B10:B50)

- sum column A when column B is >10 and column C is "blue"
=SUM(IF(B10:B50>10,1,0)*IF(C10:C50="blue",1,0)*A10:A50)
[don't use AND(), see below]

- sum column A when column B is >10 or column C is "blue"
=SUM(SIGN(IF(B10:B50>10,1,0)+IF(C10:C50="blue",1,0))*A10:A50)
[don't use OR(), see below]

 

Continue to:

• prev: 10.17.1 Why do my User-Defined Functions return #VALUE! occasionally? (spreadsheets: Excel)
• Index
• next: 10.18.2 How come my SUM(IF()) array formula doesn't work? (spreadsheets: Excel)

Share and Enjoy

Tags

business, spreadsheets, Excel, Quattro, Lotus 1-2-3, free, open source, commercial, tab delimited, csv, formula, macro