View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
PCLIVE PCLIVE is offline
external usenet poster
 
Posts: 1,311
Default Counting on two separate values

You could use SUMPRODUCT.

=SUMPRODUCT(--(MONTH(C6:C53)=7),--(YEAR(C6:C53)=2007),--(Q6:Q53<31))

If the month, year and less than amount are in a cell, say A1, B1 and C1:

=SUMPRODUCT(--(MONTH(C6:C53)=A1),--(YEAR(C6:C53)=B1),--(Q6:Q53<C1))

HTH,
Paul


--

"Leif" wrote in message
...
I'm trying to count the number of values, within a column over multiple
rows,
that fall within a range. I can do that with the COUNTIF function as
follows:

=COUNTIF(Q6:Q53,"<31")

However, now I want to extend that to break of the counts by month. I
have
a date in column C.

If I create twelve row, each with a month/year, I want to use the
month/year
against column C. Something like below (which does not work).

=IF(format(C6:C53,"MMYY")=format(A1, "MMYY"),COUNTIF(Q6:Q53,"<31"),0)

where cell A1 contains a date like 1-Jul-2007. For cell B I would like a
count where the month/year in C6:C53 is Jul-2007 AND the value in the
range
Q6:Q53 is less than 31.

Thanks
Leif