sumproduct sumifs multiple criteria

[calcnerd]

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!

 

[Special-K99]

Why do you need the braces?
This should work

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

 

[Aryatect]

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:

	B	C	D
A
A
Ab
B
B
A
B
Ac
B

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]-1[/TD]

[TD="align: right"]-6[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]-2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]-3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]-2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]-3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D1[/TH]
[TD="align: left"]=SUMIFS(B1:B9,C1:C9,"A"&"*",B1:B9,"<0")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[calcnerd]

Why do you need the braces?
This should work

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

hmm, I'm not sure...I've been using this formula forever. Maybe an older version of excel needed it?

 

[calcnerd]

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:

	B	C	D
A
A
Ab
B
B
A
B
Ac
B

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]-1[/TD]

[TD="align: right"]-6[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]-2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]-3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]-2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]-3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]D1[/TH]
[TD="align: left"]=SUMIFS(B1:B9,C1:C9,"A"&"*",B1:B9,"<0")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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