Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Look up info in a database using 2 conditions | Excel Worksheet Functions | |||
Move multiple rows of data that are not sequential | Excel Discussion (Misc queries) | |||
Why does the Data Analysis Toolbar Disappear | Excel Discussion (Misc queries) | |||
merge multiple row cells of text corresponding to one row of data | Excel Worksheet Functions | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |