Countif & Month
 

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).

Countif & Month
 

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).

Countif & Month
 

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 -

Countif & Month
 

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).

Countif & Month
 

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).

Countif & Month
 

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).

Countif & Month
 

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).

Countif & Month
 

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).

Countif & Month
 

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).

Countif & Month
 

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).

Countif & Month
 

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).