Counting on two separate values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Leif - 04 Feb 2008 20:48 GMT

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

Reply to this Message

PCLIVE - 04 Feb 2008 21:00 GMT

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

> I'm trying to count the number of values, within a column over multiple
> rows,
[quoted text clipped - 20 lines]
> Thanks
> Leif

Reply to this Message

Ken Johnson - 04 Feb 2008 21:26 GMT

=SUMPRODUCT(--(MONTH($C$6:$C$53)=MONTH($A$1)),--(YEAR($C$6:$C
$53)=YEAR($A$1)),--($Q$6:$Q$53<31))

Ken Johnson

Reply to this Message

Leif - 04 Feb 2008 23:28 GMT

Thanks Ken, that works. What is "--"?

> =SUMPRODUCT(--(MONTH($C$6:$C$53)=MONTH($A$1)),--(YEAR($C$6:$C
> $53)=YEAR($A$1)),--($Q$6:$Q$53<31))
>
> Ken Johnson

Reply to this Message

Bob Phillips - 04 Feb 2008 23:40 GMT

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Thanks Ken, that works. What is "--"?
>
>> =SUMPRODUCT(--(MONTH($C$6:$C$53)=MONTH($A$1)),--(YEAR($C$6:$C
>> $53)=YEAR($A$1)),--($Q$6:$Q$53<31))
>>
>> Ken Johnson

Reply to this Message

Leif - 04 Feb 2008 23:34 GMT

Thanks Paul. Very close to what I needed. Since I have my month/year in a
single column the solution provided by Ken was right on. I need to check on
"--", never heard of that before, except in the C programming language
(autodecrement), but I don't think that what it means here.

> You could use SUMPRODUCT.
>
[quoted text clipped - 31 lines]
> > Thanks
> > Leif

Reply to this Message