count cells in a table that meet multiple criteria

[Tammy]

I have a table that I want to set up formulas on a separate sheet for cells
to be counted that must meet several criteria. My table does have blank
cells, and I only want the cells counted that meet each condition from
different columns. Does this make any sense? The headings are set up in
columns and for an example I only want to count 1 criteria from column A, 1
from B and so on.

 

[Pete_UK]

Suppose your table is on Sheet1, occupies 100 rows, and you want to
count the number of cells that have conditions met in columns A, B and
C.You could use something like this in cell D2 of Sheet2:

=SUMPRODUCT((Sheet1!A$2:A$100=A2)*(Sheet1!B$2:B$100=B2)*(Sheet1!C$2:C
$100=C2))

where A2 in Sheet2 contains the criteria that you want to test for
column A of Sheet1, B2 contains the condition for column B
comparisons, and C2 for column C condition. The formula assumes
"equals" the condition, but you can change the = to other operators
like <, or >= etc.

Hope this helps.

Pete