sumproduct sumifs multiple criteria

calcnerd

New Member
Joined
Aug 3, 2011
Messages
24
I currently have a sumproduct sumif formula pulling wildcards and want to change it to sumifs where it only includes values <0. Here is my current formula:

=SUMPRODUCT(SUMIF($C:$C,"*"&{"ACORNS"}&"*",$D:$D))

i tried changing it to this, but it returned 0, when there are definitely values for it:
=SUMPRODUCT(SUMIFS($D:$D,$C:$C,"*"&{"ACORNS"}&"*",$C:$C,"<0"))

any ideas? thanks in advance!
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Why do you need the braces?
This should work

=SUMPRODUCT(SUMIF($C:$C,"*ACORNS*",$D:$D))
 
Last edited:
Upvote 0
Hi,

Does the values in C Column are all numbers ? You can use =ISNUMBER(C1) and copy it down and check that all the values should be TRUE.

I tried replicating your case it seemed to work for me:

BCD
1-1A-6
20A
3-2Ab
4-3B
5-2B
61A
72B
8-3Ac
93B

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
D1=SUMIFS(B1:B9,C1:C9,"A"&"*",B1:B9,"<0")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
Hi,

Does the values in C Column are all numbers ? You can use =ISNUMBER(C1) and copy it down and check that all the values should be TRUE.

I tried replicating your case it seemed to work for me:

BCD
1-1A-6
20A
3-2Ab
4-3B
5-2B
61A
72B
8-3Ac
93B

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
D1=SUMIFS(B1:B9,C1:C9,"A"&"*",B1:B9,"<0")

<tbody>
</tbody>

<tbody>
</tbody>

Thanks! With a couple modifications, this worked as well.
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top