Using Countifs with multiple "Trues"

[Befuddled]

Here is the formula I am using now:

=COUNTIFS(Table8[Column1],"="&B$2,Table8[Value for Chart],A$2)+COUNTIFS(Table1[Column1],"="&B$2,Table1[Value for Chart],A$2)

I would like to search for 6 items, instead of what is in A$2. I have these in a named range called "Stamping". What would be a good way to do this?

I tried OR(Stamping)

and OR($B$40:$B$46) which is where 'Stamping' is.

[pike]

Hi Befuddled

try..

=COUNTIFS(Table8[Column1],B$2,Table8[Value for Chart],A$2,Table1[Column1],B$2,Table1[Value for Chart],A$2)

[Befuddled]

The problem area for me is in the A$2, not with the "="& part, I want the formula to look for 6 items, and count every time any of them pop up.

I would like to search for 6 items, instead of what is in A$2. I have these in a named range called "Stamping". What would be a good way to do this?

I tried OR(Stamping)

and OR($B$40:$B$46) which is where 'Stamping' is.

[pike]

do you have a sample to play with?
try this if its not B2

=COUNTIFS(Table8[Column1],"<>"B$2,Table8[Value for Chart],A$2)+COUNTIFS(Table1[Column1],"="&B$2,Table1[Value for Chart],A$2)

[Befuddled]

Sorry, I must not have explained it very well. I am trying to change a different part of the original formula:

=COUNTIFS(Table8[Column1],"="&B$2,Table8[Value for Chart],A$2)+COUNTIFS(Table1[Column1],"="&B$2,Table1[Value for Chart],A$2)

I am trying to put something in the place of each of the "A$2"'s that makes it search for multiple things, like the words: Bob, Dave, Joe, and Matt. nothing I have tried has worked.

[DonkeyOte]

I think you will be best served reverting to a SUMPRODUCT - more expensive but more succinct.

So, if I've understood, perhaps:

Please Login or Register  to view this content.

[Befuddled]

I will try that. While I am asking questions... Where I have "Table1" is there any way to make that "Table"&$A$6 ? so that I can have the formula look int different Tables depending on what is in $A$6 ?

[DonkeyOte]

You would have to use INDIRECT at which point you're making the SUMPRODUCTs Volatile which IMO is a very bad idea I'm afraid...

You can certainly try it and test for performance impact but generally speaking Volatile Arrays are best avoided.

As an indication of syntax required:

Please Login or Register  to view this content.

so you must INDIRECT to create each range referenced

[Befuddled]

That formula works great. Thank you