Excel 2007 Countifs macro with multiple criteria (OR)

T

Tapani Mikkola

Been working on this reporting macro for excel 2007. Cant figure out how to
make countifs work for many possible values in one cell.

I have report with multiple colums. With no problem I can check if call has
been on hold for less than 26 seconds etc.

But in the same countifs sentence I should check also if colum H cell has
one of correct names from 50 possible names. There is something like 50 names
and about 30 names should be counted and the other 20 not. So I think I need
somekind of a OR sentence inside criteria (also saw one possible solution
that there would be possible to use array in the criteria)

So I have sentence like this:

x.CountIfs(Range("C:C"), "name here", Range("E:E"), puhlinja)

And should make it something like this:

x.CountIfs(Range("C:C"), {"name here", "next name", "another name"},
Range("E:E"), puhlinja)

And that should work as OR so it would check all those names from there. Any
of names written should give true value.

Hope someone understands what I'm trying to achieve here :).
 
R

Ron Rosenfeld

On Fri, 19 Mar 2010 02:44:02 -0700, Tapani Mikkola <Tapani
Been working on this reporting macro for excel 2007. Cant figure out how to
make countifs work for many possible values in one cell.

I have report with multiple colums. With no problem I can check if call has
been on hold for less than 26 seconds etc.

But in the same countifs sentence I should check also if colum H cell has
one of correct names from 50 possible names. There is something like 50 names
and about 30 names should be counted and the other 20 not. So I think I need
somekind of a OR sentence inside criteria (also saw one possible solution
that there would be possible to use array in the criteria)

So I have sentence like this:

x.CountIfs(Range("C:C"), "name here", Range("E:E"), puhlinja)

And should make it something like this:

x.CountIfs(Range("C:C"), {"name here", "next name", "another name"},
Range("E:E"), puhlinja)

And that should work as OR so it would check all those names from there. Any
of names written should give true value.

Hope someone understands what I'm trying to achieve here :).

One solution might be to ADD separate countifs.

Also, consider using Sumproduct
--ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top