Prev: Extract the month name from a month
Next: If statement
From: Tammy on 3 Feb 2010 14:56
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.
From: Pete_UK on 3 Feb 2010 19:42
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

On Feb 3, 7:56 pm, Tammy <Ta...(a)discussions.microsoft.com> wrote:
> 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.

 | 
Pages: 1
Prev: Extract the month name from a month
Next: If statement