Using COUNTIF or VLOOKUP with 2 or more conditions?

[d_whys]

Hello! Excel beginner here.

Is it possible to use COUNTIF or VLOOKUP with 2 or more conditions? Is there another function for this kind of thing?

For example, I use both functions to return the frequency of a cell value from a row or tab (countif and vlookup respectively if I'm correct) like a color, and then I would like it to return the frequency of a cell value only if it also fits another condition in another column (ie. green + medium).

Thanks!

-David

[Pete_UK]

Take a look at the COUNTIFS function - it allows you to set up more than one condition for the count.

Hope this helps.

Pete

[Obsessed]

Depending on how much data you have, I'd recommend taking both conditions and create a "concatenation key" and do a single vlookup or countif off that...reason being is countifs, sumifs, etc, are actually array formulas and are compute-intensive.

[Tony Valko]

Can you post a SMALL sample file and show us what result you expect?

A SMALL file will have about 20 rows worth of data.

[d_whys]

Pete_UK: exactly what i needed, thank you so much!

Obsessed: will keep that one for the notes, thank you!

Tony: got what i was looking for, thanks!

[Tony Valko]

Good deal. Thanks for the feedback!

[FDibbins]

Depending on how much data you have, I'd recommend taking both conditions and create a "concatenation key" and do a single vlookup or countif off that...reason being is countifs, sumifs, etc, are actually array formulas and are compute-intensive.

Actually, the xxxxIF range of formulas are far quicker and use less resources than older-type formulas. And as for them being ARRAY formulas, I have never heard that before

[Obsessed]

Actually, the xxxxIF range of formulas are far quicker and use less resources than older-type formulas. And as for them being ARRAY formulas, I have never heard that before

What do you mean "older style formulas?"

I had read that in a book that they were entered as regular formulas, but actually calculated the same way array formulas are...All I know is If I do a concatenation key of say, "Blue:Car" rather than "Blue" and "Car" in separate fields, it calculates exponentially quicker, so I never questioned what I read as it made sense in practice from my first hand experience. Maybe I'm wrong though.

[FDibbins]

I was referring to sumproduct() and sum(if()) type of formulas

[Tony Valko]

it calculates exponentially quicker

There is calculation timer code here:

http://msdn.microsoft.com/en-us/library/aa730921.aspx