COUNTIF formula with CONTAINS on a range

[Tienou]

Hi,


I have been working on it for a while and looking many threads and forums, but coulnd't find the answer.

Maybe one of you will be able to help, hopefully
I'd appreciate it.


I am trying to do a COUNTIF on a range only if a specific text on cells of another range is matching what I want.

For example:

In column A, I will have repeated values: A, B, C
In column B, I will have repeated values: X, Y, Z


I want the formula to find how many times I have value A in range column A:A only if value in column B is Y corresponding to column A.

The formula I worked on unsuccessfully was:
=--IF(ISNUMBER(FIND("Fred",A:A)),COUNTIF(B:B,F6))


I'm not sure if this is very clear, but if you have an idea (or want more details), I'd be happy to have your thoughts.

Thank you in advance

Tienou

[Moo the Dog]

You can try using the COUNTIFS function if you are using Excel 2007+. If your ranges are A1:A21 and B1:B21, try this:

=COUNTIFS(A1:A21,"A",B1:B21,"Y")

- Moo

(That formula will return the # of rows where 'A' is in column A, and 'Y' is in column B.)

[Moo the Dog]

If you are using Excel 2003, then you would have to use this: =SUMPRODUCT((A1:A21="A")*(B1:B21="Y"))

Excel 2003 doesn't have the COUNTIFS function.

The SUMPRODUCT formula will return the same results as the COUNTIFS formula in the post above.

- Moo

[Tienou]

Hi Moo,


Thanks a million.

I only have 2003 with me right now, but I'll try it as soon as I have access to 2007 (tomorrow).


Can't wait to test the COUNTIFS.

Will let you know.


Thanks again.

Tienou

[Moo the Dog]

See my other post (just above your reply) that provides the formula that needs to be used in Excel 2003. It will work in Excel 2003 and any newer versions.

- Moo

[Tienou]

I've just tried the SUMPRODUCT function and it works a treat.

What can I say beside 3 things:
- you're a star
- I can't believe I didn't see that one
- I love learning


Will test with COUNTIFS tomorrow, as it looks "cleaner" even if this is the same result.

Thanks again Moo, this is great.

[Moo the Dog]

You're welcome. Glad to help.