SUMPRODUCT that contains multiple criteria
 

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.

SUMPRODUCT that contains multiple criteria
 

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

On Sep 2, 9:57*am, Sarah (OGI)
wrote:
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.

SUMPRODUCT that contains multiple criteria
 

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

"Sarah (OGI)" wrote in message
...
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.

SUMPRODUCT that contains multiple criteria
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sarah (OGI)" wrote in message
...
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.