Putting a concatenate inside of a count if

Question

Alright, I have two excel functions that work. I want to combine them into one. Here are the two that work:

=COUNTIF('ALL EE Active BEG PERIOD'!$A:$B, 'HC Summary Details'!$A6)

=CONCATENATE('ALL EE Active BEG PERIOD'!A2," --- ",'ALL EE Active BEG PERIOD'!B2)

I thought that maybe I could combine them as follows, but it's not working - where am I going wrong?

=COUNTIF(CONCATENATE('ALL EE Active BEG PERIOD'!A2,' --- ','ALL EE Active BEG PERIOD'!B2)),'HC Summary Details'!$A6)

Answer 1

The first part of a COUNTIF has to be a range, whereas you have a single text value with CONCATENATE... Unfortunately, it doesn't seem as if COUNTIF can handle arrays either, since I would have suggested =COUNTIF(CONCATENATE('ALL EE Active BEG PERIOD'!A:A,' --- ','ALL EE Active BEG PERIOD'!B:B)),'HC Summary Details'!$A6)

Anyway, there's another way of doing this, you can use SUMPRODUCT and and IF with CONCATENATE:

=SUMPRODUCT(IF(CONCATENATE('ALL EE Active BEG PERIOD'!A:A," --- ",'ALL EE Active BEG PERIOD'!B:B)='HC Summary Details'!$A6,1,0))

After you typed that, press Ctrl+Shift+Enter to get the desired result.

Pressing Enter alone will return you the value of the first term in the result array, which you don't want.

What this does is check whether the concatenate matches what is found in A6 of the other sheet, if yes, give 1, otherwise 0. SUMPRODUCT adds all the those 1 and 0 together.

Answer 2

If you concatenate the range parameters into a string in another cell e.g. X1, and use COUNTIF(INDIRECT(X1),value) it seems that COUNTIF accepts the string as a range.