CountIf statement trouble 1

[madrappin]

I am trying to use the countif() function, however I need to specify 2 sets of criteria. Basically, I need to write something like

=CountIf(A1:A20,"Apples" and "Oranges")

I know what I wrote above wont work the way I have it, but is there anyway I can get the desired affect? Thanks.

 

[ChrisBurch]

=COUNTIF(A1:A20,"apples") + COUNTIF(A1:A20,"Oranges")

Chris

IT would be the perfect job......if it didn't have users!!!

 

[AnotherHiggins]

If Apples and Oranges are in the same range, then you can just use
=countif(A1:A20,"Apples")+countif(A1:A20,"Oranges")

Is that what you're looking for?

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[madrappin]

Hmmm, not quite. Because the two sets of criteria are in different ranges. I want to count the number of rows that have "apples" in one column and "oranges" in a second column. I appreciate your input. Thanks.

 

[KenWright]

=SUMPRODUCT((Range1="Apples")*(Range2="Oranges"))

Ranges must be identical in size

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[DPlank]

Was just going to suggest SUMPRODUCT.

Since Ken and TonyJollans introduced me to it the other week, I've rationalised a heavy maintenance spreadsheet into a self maintaining one.

It's a well useful function

Dave

"Probably the only Test Analyst on Tek-Tips"

Take a look at Forum1393 & sign up if you'd like

 

[madrappin]

But that gives me the product of the two different ranges doesn't it? I need it to count a range.

My actual spreadsheet is much larger this is just an example.

1 2
1 A B
2 A A
3 C A

Something like that, and i want to count the rows where the data in both columns 1 and 2 is equal to A. So I would have a function that for this example table would give me the result of 1. Hope that clears it up a little. Thanks.

 

[slurpee55]

Well, this is sloppy, and I am sure someone else knows a better way, but you could have a column listing =EXACT(A1,B1), =EXACT(A2,B2), etc, and then have a cell with (for your given example) =COUNTIF(C1:C3,TRUE)
This will return a count of 1.

 

[slurpee55]

Ah, just to make things clearer, I had put my EXACT statements in column C

 

[AnotherHiggins]

madrappin,

No, it doesn't add what's found in both ranges. Just try Ken's formula with your example data:

[COLOR=blue white]=sumproduct((A1:A10="A")*(B1:B10="B"))[/color]

slurpee55,

Try the sumproduct formula. It's really quite nice.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[slurpee55]

Sumproduct is great when you want to specify the exact items, as appears to be the case here, you are right, except your formula should be =sumproduct((A1:A10="A")*(B1:B10="A")) to count the times where A occurs in both columns. Thanks for the advice, though - sumproduct is a powerful formula item.

 

[madrappin]

Ok, thats perfect! What I was doing wrong was, rather than selecting a range like A1:A10, I assumed doing A:A was acceptible and it was giving me an error doing it that way. I guess thats because the two ranges have to be equal sizes? Whatever the reason, you all helped me out a lot. Thanks

 

[AnotherHiggins]

madrappin,

Unfortunately, sumproduct doesn't support using an entire column. You can use
[COLOR=blue white]=SUMPRODUCT((A1:A65535="A")*(B1:B65535="A"))[/color]
(there are 65536 rows in excel)

slurpee55,

Thanks for pointing out the "A" thing - I didn't read the post carefully enough.

I just wanted to steer madrappin towards sumproduct. The process you propose doesn't check to see what the values of the cells are. It only checks to see if the cells beside one another are equal - whether they be "A" or "lknasdf".

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[slurpee55]

I hope it isn't "lknasdf"!
As long as we're on the subject, is there a way to use sumproduct to count all the cases where the two do not match?
That is, to use the example above, to get a return of 2 (for rows 1 and 3)?

 

[madrappin]

I think if you use <> instead of = it should work.

 

[slurpee55]

Yes, it does, thanks.
Been informative.

 

[AnotherHiggins]

For a really good explanation of sumproduct, see faq68-4725 by bluedragon2.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[slurpee55]

Just to let you know, I was able to take this new sumproduct knowledge and use it already, AND I used a variant of it to help someone at another site!