cell reference in sumifs

[Jai]

Hi,

I want to use

=SUM(SUMIFS(W:W,L:L,{"DR","SS"},S:S,B5))

which works but I want the {"DR","SS"} arguement to come from a cell
reference. i.e =SUM(SUMIFS(W:W,L:L,C5,S:S,B5)) where C5 is {"DR","SS"}.
Unfortunately this isn't working for me. Any suggestions? Thanks.

 

[Jai]

Thanks, but the problem is I need to use only one cell as the reference.

 

[Teethless mama]

C5: holds DR,SS

=SUMPRODUCT((ISNUMBER(SEARCH(L1:L100,C5)))*(S1:S100=B5),W1:W100)

Adjust range to suit

 

[T. Valko]

C5: holds DR,SS

That'll be treated as a single literal string. Based on the formula the OP
posted and their follow-up description, it sounds like they want to use a
single cell to hold 2 separate criteria.

While it could be done I would recommend they not do it that way.