Sum(Countifs( ... use cell reference instead of Text in constant array .....

[spydey]

I have some text in cells A3:A6.

The text is:

Testing1
Testing2
Testing3

I have a Sum(Countifs( formula in cell B7.

The formula is:

=Sum(Countifs(CriteriaRange1,{"Testing1","Testing2","Testing3"},CriteriaRange2, Criteria2, etc. etc.))

I would like to reference the cells in my array instead of having to type out the text.
The text in the cells can change from time to time and having to change the formula each time is a nuisance and a waste of time.
Simply changing the text in the cells would be much simplier.

Something like:

=Sum(Countifs(CriteriaRange1,{$A$3,$A$4,$A$5},CriteriaRange2, Criteria2, etc. etc.))

Is that possible?

I have tried and only getter a message saying that I have input my formula incorrectly.

Thoughts? Ideas?

Maybe I should use SumProduct?????

-Spydey

 

[Eric W]

Try:

=Sum(Countifs(CriteriaRange1,$A$3:$A$5,CriteriaRange2, Criteria2, etc. etc.))

and confirm with Control+Shift+Enter.

 

[spydey]

Try:

=Sum(Countifs(CriteriaRange1,$A$3:$A$5,CriteriaRange2, Criteria2, etc. etc.))

and confirm with Control+Shift+Enter.

Thanks Eric!

I will give that a try. I thought that I already tried a CSE input and wasn't successful, but maybe I did something incorrectly.

I will try again.

-Spydey

 

[spydey]

@Eric W

Thanks Eric!!

Totally worked.

I don't know why it didn't when I initially tried to enter a CSE array ... hmmmm, I must have missed something.

Thanks again for the quick response!!

-Spydey

 

[Eric W]

Glad it worked for you! Remember that you can have as many single conditions as you want, but a maximum of 2 group conditions, and if you have 2 groups, one must be vertical and one must be horizontal. A3:A5 and B3:D3 for example.

 

[Domenic]

I don't know why it didn't when I initially tried to enter a CSE array ... hmmmm, I must have missed something.

It's probably because you enclosed your reference within curly braces (ie. {$A$3,$A$4,$A$5} ), as you indicated in your original post. By the way, you can avoid having to confirm the formula with CONTROL+SHIFT+ENTER by replacing SUM with SUMPRODUCT.