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.


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
C5: holds DR,SS

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

Adjust range to suit


"Jai" wrote:

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

"Teethless mama" wrote:

C5: holds DR
D5: holds SS

=SUMPRODUCT(--(ISNUMBER(MATCH(L:L,C5:D5,0))),--(S:S=B5),W:W)


"Jai" wrote:

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.