Multiple conditions data analysis
 

I have a database of 50 columns and 4000 rows. I am trying to calculate the
number of rows that match multiple criteria. For example:
If column A < 0.5, Column B=0 and Column C: between 2/1/06 - 3/1/06.
Note that not all cells have data and are blank. Say some cells with A < 0.5
will not have any value for B.

Multiple conditions data analysis
 

Hi Sriram,

Try the following formula.

=SUMPRODUCT(--(A2:A1001<0.5)*ISNUMBER(A2:A1001)*(B2:B1001=0)*ISN UMBER(B2:B1001)*(C2:C1001=$E$1)*(C2:C1001<=$F$1)* ISNUMBER(C2:C1001))

where E1 and F1 contain the start and end dates (2/1/06 and 3/1/06 in your
example). If blanks occur only in Column B (and not in columns A or C), you
may remove from the formula the ISNUMBER parts corresponding to those columns.

Regards,
B. R. Ramachandran


"Sriram R" wrote:

I have a database of 50 columns and 4000 rows. I am trying to calculate the
number of rows that match multiple criteria. For example:
If column A < 0.5, Column B=0 and Column C: between 2/1/06 - 3/1/06.
Note that not all cells have data and are blank. Say some cells with A < 0.5
will not have any value for B.