Countif & Month

  • Thread starter Thread starter Bigfoot17
  • Start date Start date
B

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).
 
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
 
I understand thatthis is an array but it appears to be only looking at the
month and not the year. Is this right?
 
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
 
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?
 
Found IT! Typo!

T. Valko said:
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
Click to expand...
 
Yeah, I see I had a couple of typos in my reply. Glad you got it
straightened out!

Thanks for the feedback!
 
Your help is much appreciated. It helps me learn how to approach future
problems, etc. Thanks.
 
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.
 
Back
Top