Sumifs{}

volaxe

New Member
Joined
Mar 22, 2018
Messages
4
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>
ABCDEFGHI
2April Sales4/1/2018GO578,000.00
3P77M41PGO574/1/2018GO572,000.00
4025720100004/1/2018M41P20,000.00
54/1/201804/1/2018M41P5,700.00
64/2/201804/1/2018M41P20.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>
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,393
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

New Member
Joined
Mar 22, 2018
Messages
4
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
 

shaowu459

Well-known Member
Joined
Apr 26, 2018
Messages
505
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,109,531
Messages
5,529,389
Members
409,870
Latest member
Well59
Top