Countif & Month

[Bigfoot17]

A B C
ChoiceA ChoiceB Exp.Date
X 11/2/08
X 12/4/08
X 3/15/09
X 5/17/09
X 2/28/09
X 10/10/08

I have something similar to the above except with 2500 rows. I am looking
to have on a second sheet a count of the number of ChoiceAs and the number of
ChoiceBs that expire for each month of the year.

ChoiceA ChoiceB
Jan
Feb
Mar
April
....

(To complicate matters I have a spinner for the year so you can change the
2008 to 2009 and see the expirations in 2009).

 

[muddan madhu]

try this

for choice A
=COUNT(IF($A$2:$A$6="X",IF(MONTH($C$2:$C$6)=row(1:1),)))

and drag it down


for choice B
=COUNT(IF($B$2:$B$46="X",IF(MONTH($C$2:$C$6)=ROW(1:1),)))

and drag it down

 

[muddan madhu]

use ctrl + shift + enter once u enter the formula

 

[Bigfoot17]

I understand thatthis is an array but it appears to be only looking at the
month and not the year. Is this right?

 

[T. Valko]

Try this:

Using named ranges:

ChoiceA refers to Sheet1 A2:An
ChoiceB refers to Sheet2 B2:Bn
ExpDate refers to Sheet1 C2:Cn
Spinner refers to Sheet2 A1

Your spinner in linked to Sheet2 A1 and returns a year number like 2007,
2008, 2009 etc

Sheet2 B1 = header = Choice A
Sheet2 C1 = header = Choice B

Enter this formula in Sheet2 A2:

=DATE(Sheet2!A$1,ROWS(A$2:A2),1)

Enter this formula in Sheet2 B2:

=SUMPRODUCT(--(ChoiceA="x"),--(MONTH(ExpDate)=MONTH(A2)),--(YEAR(ExpDate)=Spinner))

Enter this formula in Sheet2 C2:

=SUMPRODUCT(--(ChoiceB="x"),--(MONTH(ExpDate)=MONTH(A2)),--(YEAR(ExpDate)=Spinner))

Select cells A2:C2 then copy down to row 13.

Select the range A2:A13
Goto Format>Cells>Custom
Enter mmm for the short month name format or mmmm for the long month name
format
OK out

 

[Bigfoot17]

I could have banged on the keyboard for quite sometime and still not have
come up with this ... Thanks. One Glitch is that it runs December -November,
any suggestions?

 

[Bigfoot17]

Found IT! Typo!

Try this:

Using named ranges:

ChoiceA refers to Sheet1 A2:An
ChoiceB refers to Sheet2 B2:Bn
ExpDate refers to Sheet1 C2:Cn
Spinner refers to Sheet2 A1

Your spinner in linked to Sheet2 A1 and returns a year number like 2007,
2008, 2009 etc

Sheet2 B1 = header = Choice A
Sheet2 C1 = header = Choice B

Enter this formula in Sheet2 A2:

=DATE(Sheet2!A$1,ROWS(A$2:A2),1)

Enter this formula in Sheet2 B2:

=SUMPRODUCT(--(ChoiceA="x"),--(MONTH(ExpDate)=MONTH(A2)),--(YEAR(ExpDate)=Spinner))

Enter this formula in Sheet2 C2:

=SUMPRODUCT(--(ChoiceB="x"),--(MONTH(ExpDate)=MONTH(A2)),--(YEAR(ExpDate)=Spinner))

Select cells A2:C2 then copy down to row 13.

Select the range A2:A13
Goto Format>Cells>Custom
Enter mmm for the short month name format or mmmm for the long month name
format
OK out

 

[T. Valko]

Yeah, I see I had a couple of typos in my reply. Glad you got it
straightened out!

Thanks for the feedback!

 

[Bigfoot17]

Your help is much appreciated. It helps me learn how to approach future
problems, etc. Thanks.

 

[Bigfoot17]

I need to tell you how much I appreciate your help with this problem. I have
since been able to apply it in a couple of similar situations. Thanks again.

 

[T. Valko]

You're quite welcome!