SUMPRODUCT that contains multiple criteria

[Sarah (OGI)]

I have written the following formula, but an #N/A value is being returned.

=SUMPRODUCT(--(YTD!$B$2:$B$65536={"*7*","*16*"}),--(YTD!$AB$2:$AB$65536))

I am trying to sum the cells in column AB where the cell in column B
contains either a 7 or a 16.

Please could someone advise if I need to make a correction to the existing
formula, or if I should be using a different function altogether.

Many thanks in advance.

 

[Pete_UK]

You can't use wildcards in this situation, but as you have only got
one criterion, you could use SUMIF which does support wildcards:

=SUMIF(YTD!B:B,"*7*",YTD!AB:AB) + SUMIF(YTD!B:B,"*16*",YTD!AB:AB)

Hope this helps.

Pete

 

[Ashish Mathur]

Hi,

You could try this

In range F14:F15, you have *7* and *16*. In cell F13 you have Name and in
cell G13, you have Amount. In G14, you have
=DSUM($B$2:$B$65536,G13,F13:F15). In cell B1 you have Name and in cell AB1,
you have Amount

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Don Guillett]

Define "contains". Do you mean only 7 or only 16 in col B or aaaa7bbb
aaa16bbb