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>

A	B	C	D	E	F	G	H	I
2		April Sales		4/1/2018	GO57	8,000.00
3		P77	M41P	GO57		4/1/2018	GO57	2,000.00
4		0	25720	10000		4/1/2018	M41P	20,000.00
5	4/1/2018	0		4/1/2018	M41P	5,700.00
6	4/2/2018	0		4/1/2018	M41P	20.00

<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>
</tbody>

 

[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).