# Thread: sumproduct sumifs multiple criteria Thanks: 0 Likes: 0

1. ## sumproduct sumifs multiple criteria

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"))

2. ## Re: sumproduct sumifs multiple criteria

Why do you need the braces?
This should work

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

3. ## Re: sumproduct sumifs multiple criteria

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
1 -1 A -6
2 0 A
3 -2 Ab
4 -3 B
5 -2 B
6 1 A
7 2 B
8 -3 Ac
9 3 B
Sheet1

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

4. ## Re: sumproduct sumifs multiple criteria

Originally Posted by Special-K99
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?

5. ## Re: sumproduct sumifs multiple criteria

Originally Posted by 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
1 -1 A -6
2 0 A
3 -2 Ab
4 -3 B
5 -2 B
6 1 A
7 2 B
8 -3 Ac
9 3 B
Sheet1

Worksheet Formulas
Cell Formula
D1 =SUMIFS(B1:B9,C1:C9,"A"&"*",B1:B9,"<0")
Thanks! With a couple modifications, this worked as well.