Excel 2007 Countifs macro with multiple criteria (OR)

[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 .

 

[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

 

[Herbert Seidenberg]

Excel 2007 PivotTable
A no code, no formula approach.
http://c0718892.cdn.cloudfiles.rackspacecloud.com/03_19_10b.xlsx
Pdf preview:
http://www.mediafire.com/file/1lm13iymumy/03_19_10b.pdf