How to give a cell as a reference in the sumifs function?

[Dhanola]

Hey, there I have to give the criteria of the sumifs function as a reference to another cell "$Q$4" i.e. Q4

with the value as

{"*GARVIT RECOVERED CELL*","*RAHUL RECOVERED CELL*","*HARSH RECOVERED CELL*"}

which is stored in the cell Q4 but excel is not giving the correct output and showing 0.

the formula without reference works i.e.

=SUMIFS(C600:O600,B600:N600,{"*GARVIT RECOVERED CELL*","*RAHUL RECOVERED CELL*","*HARSH RECOVERED CELL*"})

will give the result but the reference cell location as :

=SUMIFS(C600:O600,B600:N600,$Q$4)

not give the same sum and show 0.

the formula is just checking if the string in the given cell range contains any of the three list values shown above in curly braces.

[#2]

@Dhanola 

In the formula =SUMIFS(C600:O600,B600:N600,{"*GARVIT RECOVERED CELL*","*RAHUL RECOVERED CELL*","*HARSH RECOVERED CELL*"}) the { } create an array of three text values.

But if you enter {"*GARVIT RECOVERED CELL*","*RAHUL RECOVERED CELL*","*HARSH RECOVERED CELL*"} in a cell and use that cell, the value {"*GARVIT RECOVERED CELL*","*RAHUL RECOVERED CELL*","*HARSH RECOVERED CELL*"} becomes a single text string instead of an array.

Try the following:

Enter *GARVIT RECOVERED CELL* in Q4, *RAHUL RECOVERED CELL* in Q5 and *HARSH RECOVERED CELL* in Q6.

And use =SUMIFS(C600:O600,B600:N600,$Q$4:$Q$6)