How to use COUNT using multiple ranges

[LIZZIE]

I need the count of information based on criteria from differen
columns. Example of columns:

A B C D
0_F JJ 1 II
0_F JD 2 III
0_R JR 1 II
0_F JC 2 II
Blank JP 1 III
0_F JK 1 III


What is the count IF (Col A)=0_F and IF (Col D)=II

I don't know how to combine the information to come up with the resul
of 2

Thanks for any help

 

[Domenic]

On 5/9/04 7:38 AM, in article (e-mail address removed), "LIZZIE

" <<[email protected]> wrote:

I need the count of information based on criteria from different
columns. Example of columns:

A B C D
0_F JJ 1 II
0_F JD 2 III
0_R JR 1 II
0_F JC 2 II
Blank JP 1 III
0_F JK 1 III


What is the count IF (Col A)=0_F and IF (Col D)=II

I don't know how to combine the information to come up with the result
of 2

Thanks for any help.

Hi Lizzie,

One way:

=SUM(IF((A1:A6="O_F")*(D16="II"),1,0)), to be entered using
Ctrl+Shift+Enter

Cheers!

 

[Dave Peterson]

One way:

=sumproduct(--(a1:a1000="o_f"),--(d1:d1000="ii"))

 

[Tushar Mehta]

You have already received single-cell formulas, but if you want to
break down the computation into components...

Suppose your data start in row 2. Then, in J2, enter the formula =AND
(A2="0_F",D2="II"). Copy J2 down to rows 3:7. In J8 enter the formula
=COUNTIF(J2:J7,TRUE)

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[JMay]

Domenic:
Thanks for your answer. It works great. Playing around with alternatives,
I can't get this to work. I'm sure there's a reason why not !! I can't see
it. Can you? If so provide my illogic.

{=SUM(IF(AND(COUNTIF(A1:A6,"O_F"),COUNTIF(D16,"II")),1,0))}

Tks in Advance.......
JMay

 

[Domenic]

Domenic:
Thanks for your answer. It works great. Playing around with alternatives,
I can't get this to work. I'm sure there's a reason why not !! I can't see
it. Can you? If so provide my illogic.

{=SUM(IF(AND(COUNTIF(A1:A6,"O_F"),COUNTIF(D16,"II")),1,0))}

Tks in Advance.......
JMay

Oh...so close!

The problem with that formula is that the IF statement requires a logical
expression evaluating to TRUE or FALSE.

COUNTIF(A1:A6,"O_F") and COUNTIF(D16,"II") by themselves are not logical
expressions.

This would work,

{=SUM(IF(AND(COUNTIF(A1:A6,"O_F")>0,COUNTIF(D16,"II")>0)),1,0))}

but I don't think it would be the right construct to achieve the desired
results.

 

[JMay]

D - Thanks, you say "This would work,"
I had to remove the ")" eighth character from the end;
but entered C+S+E
But still wrong results,
Thanks for assisting...

 

[Domenic]

I had to remove the ")" eighth character from the end;
but entered C+S+E

Oops! That was a typing mistake on my part.

But still wrong results,

That's right. That's because the first COUNTIF evaluates to TRUE, since
there are one or more cells that meet the criteria, and the second COUNTIF
also evaluates to TRUE, adding 1 to your calculations, and giving you a
result of 1.

That's why I said I don't think this is the right formula to use in this
situation. But the syntax is correct, which is what I thought you were more
concerned with.

 

[JMay]

Re: How to use COUNT using multiple rangesDomenic;
You were most helpful.
Thank you,
JMay

I had to remove the ")" eighth character from the end;
but entered C+S+E

Oops! That was a typing mistake on my part.

But still wrong results,

That's right. That's because the first COUNTIF evaluates to TRUE, since there are one or more cells that meet the criteria, and the second COUNTIF also evaluates to TRUE, adding 1 to your calculations, and giving you a result of 1.

That's why I said I don't think this is the right formula to use in this situation. But the syntax is correct, which is what I thought you were more concerned with.

 

[Domenic]

Domenic;
You were most helpful.
Thank you,
JMay

JMay, you¹re welcome!