Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT with multiple criteria | Excel Worksheet Functions | |||
Sumproduct-multiple criteria = and not = | Excel Worksheet Functions | |||
SUMPRODUCT / SUMIF on Multiple Worksheets with Multiple Criteria | Excel Discussion (Misc queries) | |||
Sumproduct multiple criteria | Excel Worksheet Functions | |||
multiple criteria in if or sumproduct | Excel Worksheet Functions |