Remember Me?

#1
March 31st 05, 03:43 PM
 maxtrixx Posts: n/a
Combining IF and COUNTIF based on two columns

Here is what I'm trying to do, I have two colums with data, the first column
will have one of four letters (A, B, C, D) the second column will have one of
two numbers (0, 1). I need to get 6 different counts:

COUNTIF column A range A1:A250 is A
COUNTIF column A is range A1:A250 B
COUNTIF column A is range A1:A250 C
COUNTIF column A is range A1:A250 D
COUNT column A ONLY IF column B range B1:B250 is 0
COUNT column A ONLY IF cloumn B range B1:B250 is 1

I can do the first 4 calculations pretty easy but am having a hard time
figuring out how to do the last two calculations. Can anyone help me?

#2
March 31st 05, 03:59 PM
 Duke Carey Posts: n/a

try

=SUMPRODUCT(--(A1:A250="A"),--(B1:B250=0))

"maxtrixx" wrote:

Here is what I'm trying to do, I have two colums with data, the first column
will have one of four letters (A, B, C, D) the second column will have one of
two numbers (0, 1). I need to get 6 different counts:

COUNTIF column A range A1:A250 is A
COUNTIF column A is range A1:A250 B
COUNTIF column A is range A1:A250 C
COUNTIF column A is range A1:A250 D
COUNT column A ONLY IF column B range B1:B250 is 0
COUNT column A ONLY IF cloumn B range B1:B250 is 1

I can do the first 4 calculations pretty easy but am having a hard time
figuring out how to do the last two calculations. Can anyone help me?

#3
March 31st 05, 04:59 PM
 maxtrixx Posts: n/a

Awesome, thank you, works like a charm. Now is it possible to include some
date ranges like from 1/1/2005 to 1/31/2005? I need this by month. I forgot
to include that in the original question.

"Duke Carey" wrote:

try

=SUMPRODUCT(--(A1:A250="A"),--(B1:B250=0))

"maxtrixx" wrote:

Here is what I'm trying to do, I have two colums with data, the first column
will have one of four letters (A, B, C, D) the second column will have one of
two numbers (0, 1). I need to get 6 different counts:

COUNTIF column A range A1:A250 is A
COUNTIF column A is range A1:A250 B
COUNTIF column A is range A1:A250 C
COUNTIF column A is range A1:A250 D
COUNT column A ONLY IF column B range B1:B250 is 0
COUNT column A ONLY IF cloumn B range B1:B250 is 1

I can do the first 4 calculations pretty easy but am having a hard time
figuring out how to do the last two calculations. Can anyone help me?

#4
March 31st 05, 05:07 PM
 Duke Carey Posts: n/a

Thanks for the feedback.

As for the second question, adjust this formula to reflect your ranges

=SUMPRODUCT(--(A1:A250="A"),--(B1:B250=1),--(MONTH(C1:C250)=1))

Duke

"maxtrixx" wrote:

Awesome, thank you, works like a charm. Now is it possible to include some
date ranges like from 1/1/2005 to 1/31/2005? I need this by month. I forgot
to include that in the original question.

"Duke Carey" wrote:

try

=SUMPRODUCT(--(A1:A250="A"),--(B1:B250=0))

"maxtrixx" wrote:

Here is what I'm trying to do, I have two colums with data, the first column
will have one of four letters (A, B, C, D) the second column will have one of
two numbers (0, 1). I need to get 6 different counts:

COUNTIF column A range A1:A250 is A
COUNTIF column A is range A1:A250 B
COUNTIF column A is range A1:A250 C
COUNTIF column A is range A1:A250 D
COUNT column A ONLY IF column B range B1:B250 is 0
COUNT column A ONLY IF cloumn B range B1:B250 is 1

I can do the first 4 calculations pretty easy but am having a hard time
figuring out how to do the last two calculations. Can anyone help me?

#5
March 31st 05, 05:31 PM
 maxtrixx Posts: n/a

Duke,

I understand the MONTH function and it works on individual cells but it
doesn't work inside the SUMPRODUCT function. I have the column where the
dates are formated to Date in the 3/1498 format. Do you know how to get
around this? Thanks again for the quick reply.

"Duke Carey" wrote:

Thanks for the feedback.

As for the second question, adjust this formula to reflect your ranges

=SUMPRODUCT(--(A1:A250="A"),--(B1:B250=1),--(MONTH(C1:C250)=1))

Duke

"maxtrixx" wrote:

Awesome, thank you, works like a charm. Now is it possible to include some
date ranges like from 1/1/2005 to 1/31/2005? I need this by month. I forgot
to include that in the original question.

"Duke Carey" wrote:

try

=SUMPRODUCT(--(A1:A250="A"),--(B1:B250=0))

"maxtrixx" wrote:

Here is what I'm trying to do, I have two colums with data, the first column
will have one of four letters (A, B, C, D) the second column will have one of
two numbers (0, 1). I need to get 6 different counts:

COUNTIF column A range A1:A250 is A
COUNTIF column A is range A1:A250 B
COUNTIF column A is range A1:A250 C
COUNTIF column A is range A1:A250 D
COUNT column A ONLY IF column B range B1:B250 is 0
COUNT column A ONLY IF cloumn B range B1:B250 is 1

I can do the first 4 calculations pretty easy but am having a hard time
figuring out how to do the last two calculations. Can anyone help me?

#6
March 31st 05, 06:21 PM
 maxtrixx Posts: n/a

I cleaned up the column and it worked perfectly. I imported the data from a
text file and some of the fields had a "." in them so that's where the error
was. Thank you very much!!

"Duke Carey" wrote:

Thanks for the feedback.

As for the second question, adjust this formula to reflect your ranges

=SUMPRODUCT(--(A1:A250="A"),--(B1:B250=1),--(MONTH(C1:C250)=1))

Duke

"maxtrixx" wrote:

Awesome, thank you, works like a charm. Now is it possible to include some
date ranges like from 1/1/2005 to 1/31/2005? I need this by month. I forgot
to include that in the original question.

"Duke Carey" wrote:

try

=SUMPRODUCT(--(A1:A250="A"),--(B1:B250=0))

"maxtrixx" wrote:

Here is what I'm trying to do, I have two colums with data, the first column
will have one of four letters (A, B, C, D) the second column will have one of
two numbers (0, 1). I need to get 6 different counts:

COUNTIF column A range A1:A250 is A
COUNTIF column A is range A1:A250 B
COUNTIF column A is range A1:A250 C
COUNTIF column A is range A1:A250 D
COUNT column A ONLY IF column B range B1:B250 is 0
COUNT column A ONLY IF cloumn B range B1:B250 is 1

I can do the first 4 calculations pretty easy but am having a hard time
figuring out how to do the last two calculations. Can anyone help me?

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post DTomSimpson Excel Worksheet Functions 2 March 29th 05 04:47 AM Tricia S. Excel Worksheet Functions 10 March 17th 05 02:17 PM Heather Murch Excel Worksheet Functions 1 February 17th 05 03:47 PM Syed Ali Zubair Excel Worksheet Functions 2 February 8th 05 08:46 AM Liz G Excel Worksheet Functions 3 November 1st 04 09:34 PM

All times are GMT +1. The time now is 10:32 PM.