Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting Ps and Ls based on the date in another row.
one more problem. I have dates running from 1/1/2007 to
29/6/2007 from C2 to EB2 and I have attendance as either P or L in the row 14 (C14 to EB14). Now I want to count all the Ps in a month. I want the formula to look for month 1 in row 2 and then count Ps in row 14. Also I would need to add the count of both Ls and Ps for some other purpose. Thanks Gary |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting Ps and Ls based on the date in another row.
Try this:
I'm assuming there are no empty cells within your date range on row 2. An empty cell will evaluate as month 1. =SUMPRODUCT((MONTH(C2:EB2)=1)*(C14:EB14="P")) To count both "P" and "L": =SUMPRODUCT((MONTH(C2:EB2)=1)*(C14:EB14={"P";"L"}) ) Biff "Gary" wrote in message ... one more problem. I have dates running from 1/1/2007 to 29/6/2007 from C2 to EB2 and I have attendance as either P or L in the row 14 (C14 to EB14). Now I want to count all the Ps in a month. I want the formula to look for month 1 in row 2 and then count Ps in row 14. Also I would need to add the count of both Ls and Ps for some other purpose. Thanks Gary |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting Ps and Ls based on the date in another row.
TRY:
=sumproduct(--(month(c2:eb2)=1),--(c14:eb14="P")) hth "Gary" wrote: one more problem. I have dates running from 1/1/2007 to 29/6/2007 from C2 to EB2 and I have attendance as either P or L in the row 14 (C14 to EB14). Now I want to count all the Ps in a month. I want the formula to look for month 1 in row 2 and then count Ps in row 14. Also I would need to add the count of both Ls and Ps for some other purpose. Thanks Gary |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to create formula based on date | New Users to Excel | |||
Need to create formula based on date | New Users to Excel | |||
Isolating single row based on date comparison | Excel Worksheet Functions | |||
How to find the most recent date in a column based on other column | Excel Worksheet Functions | |||
Conditional formatting based on date range | Excel Discussion (Misc queries) |