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





On Oct 10, 7:11*pm, Bigfoot17
wrote:
* * *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).

use ctrl + shift + enter once u enter the formula





On Oct 10, 7:50*pm, muddan madhu wrote:
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

On Oct 10, 7:11*pm, Bigfoot17
wrote:



* * *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).- Hide quoted text -

- Show quoted text -

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

"muddan madhu" wrote:

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





On Oct 10, 7:11 pm, Bigfoot17
wrote:
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:

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 FormatCellsCustom
Enter mmm for the short month name format or mmmm for the long month name
format
OK out

--
Biff
Microsoft Excel MVP


"Bigfoot17" wrote in message
...
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).

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?

"T. Valko" wrote:

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 FormatCellsCustom
Enter mmm for the short month name format or mmmm for the long month name
format
OK out

--
Biff
Microsoft Excel MVP


"Bigfoot17" wrote in message
...
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).

Found IT! Typo!

"T. Valko" wrote:

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 FormatCellsCustom
Enter mmm for the short month name format or mmmm for the long month name
format
OK out

--
Biff
Microsoft Excel MVP


"Bigfoot17" wrote in message
...
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).

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

Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Bigfoot17" wrote in message
...
Found IT! Typo!

"T. Valko" wrote:

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 FormatCellsCustom
Enter mmm for the short month name format or mmmm for the long month name
format
OK out

--
Biff
Microsoft Excel MVP


"Bigfoot17" wrote in message
...
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).

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

"T. Valko" wrote:

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

Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Bigfoot17" wrote in message
...
Found IT! Typo!

"T. Valko" wrote:

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 FormatCellsCustom
Enter mmm for the short month name format or mmmm for the long month name
format
OK out

--
Biff
Microsoft Excel MVP


"Bigfoot17" wrote in message
...
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).

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" wrote:

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 FormatCellsCustom
Enter mmm for the short month name format or mmmm for the long month name
format
OK out

--
Biff
Microsoft Excel MVP


"Bigfoot17" wrote in message
...
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).

You're quite welcome!

--
Biff
Microsoft Excel MVP


"Bigfoot17" wrote in message
...
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" wrote:

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 FormatCellsCustom
Enter mmm for the short month name format or mmmm for the long month name
format
OK out

--
Biff
Microsoft Excel MVP


"Bigfoot17" wrote in message
...
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).