CODEWITHSUNDEEP
excelconcatenationexcel-formulacountif
user1475816
user1475816

Reputation: 43

Putting a concatenate inside of a count if

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)

Upvotes: 3

Views: 29375

Answers (3)

user29193099
user29193099

Reputation: 1

The second method using the INDIRECT function works and you can just simply put the CONCAT statement in to the INDIRECT function, you don't need to put it in a separate cell

Eg: COUNTIF(INDIRECT(CONCAT("Start ",A1," End")),[Criteria])

Upvotes: 0

Hugh
Hugh

Reputation: 21

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.

Upvotes: 1

Jerry
Jerry

Reputation: 71538

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.

Upvotes: 4

Related Questions