counting Ps and Ls based on the date in another row.

[Gary]

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

 

[T. Valko]

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

 

[Guest]

TRY:

=sumproduct(--(month(c2:eb2)=1),--(c14:eb14="P"))

hth