Sumifs{}

[volaxe]

Hi guys, im trying to sum the amount im the chart below but i believe the {} is wrong that it only reflects the 1st criteria. Cell C5
=sum(sumifs(I:I,H:H,{"*P77*","*M41P*","*GO57*"},G:G,B5))
Scratching head.. thank you guys!

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 77px"><col width="77"><col width="100"><col width="100"><col width="100"><col width="20"><col width="52"><col width="149"><col width="78"></colgroup><tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD]F[/TD]
[TD="align: center"]G[/TD]
[TD]H[/TD]
[TD="align: right"]I[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD][/TD]
[TD="colspan: 3"]April Sales[/TD]
[TD="bgcolor: #000000"][/TD]
[TD="align: center"]4/1/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ffff]#00ffff[/URL] "]GO57[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ffff]#00ffff[/URL] , align: right"]8,000.00[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD][/TD]
[TD]P77[/TD]
[TD]M41P[/TD]
[TD]GO57[/TD]
[TD="bgcolor: #000000"][/TD]
[TD="align: center"]4/1/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ffff]#00ffff[/URL] "]GO57[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ffff]#00ffff[/URL] , align: right"]2,000.00[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]25720[/TD]
[TD]10000[/TD]
[TD="bgcolor: #000000"][/TD]
[TD="align: center"]4/1/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ffff]#00ffff[/URL] "]M41P[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ffff]#00ffff[/URL] , align: right"]20,000.00[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]4/1/2018[/TD]
[TD="colspan: 3, align: center"]0[/TD]
[TD="bgcolor: #000000"][/TD]
[TD="align: center"]4/1/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ffff]#00ffff[/URL] "]M41P[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ffff]#00ffff[/URL] , align: right"]5,700.00[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]4/2/2018[/TD]
[TD="colspan: 3, align: center"]0[/TD]
[TD="bgcolor: #000000"][/TD]
[TD="align: center"]4/1/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ffff]#00ffff[/URL] "]M41P[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ffff]#00ffff[/URL] , align: right"]20.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Welcome to Mr Excel forum

Not sure what you need.
Your formula, =SUM(SUMIFS(I:I,H:H,{"*P77*","*M41P*","*GO57*"},G:G,B5)), returned 35720.
It seems correct to me.

M.

 

[volaxe]

Somehow in my sheet it only reflects result of "*P77*" and ignores the other 2 criteria. in my sheet the result for the formula is 0 when

 

[Aladin Akyurek]

Care to share this Google Sheet you seem to have?

 

[shaowu459]

Double click cell C5, and proceed with Ctrl+shift+enter, not just press enter.

I mean double click C5, and hold Ctrl+shift, press enter while not release Ctrl and Shift.

 

[Aladin Akyurek]

Google Sheets do not allow expressing multiple OR conditions using array constants in SumIfs (while it does in SumIf).

Invoke instead:

Either...

=sumproduct(I:I,--isnumber(match(H:H,$C$3:$E$3,0)),--(G:G=B5))

Or...

=sumproduct(I:I,--isnumber(match(H:H,{"P77","M41P","GO57"},0)),--(G:G=B5))

You don't need wildcard, given your data.

Note. This problem does not arise in Excel (hence Marcelo's reply).