SumIFs formula multiple criteria (Visible Cells)

[kac1125]

I have two tabs, one with order number, Item number, the shipped qty and cheat cell that shows a 1 if cell is visible after filtering. the second tab has just the unique Order numbers, so if there is more than one item the order number is not repeating. I am looking to sum the shipped qty for each order number that is visible

Order Number	Item Number	Shipped Qty	Visible
1538930​	1	850	1
1539857​	1	54	1
1540524​	1	54	1
1540529​	1	15	1
1540529​	2	54	1
1542289​	1	13	1
1556328​	2	80	1
1556328​	3	49	1
1556328​	4	200	1

Sheet two looks like this

Order Number	Shipped
1538930​
1539857​
1540524​
1540529​
1542289​
1556328​

I am trying the following formula in she 2 column 2 =SUMIFS(Sheet1!A:A,Sheet2!A:A,@Sheet1!C:C,Sheet1!D:D,">0") and I seem to be missing something. Please let me know if you have any ideas.

Thanks in advance!

 

[Armstrong_N14]

Hope this Helps!

AGGREGATE function - Microsoft Support

The AGGREGATE function can apply different aggregate functions to a list or database with the option to ignore hidden rows and error values.

support.microsoft.com

 

[Fluff]

How about

=SUMIFS(Sheet1!C:C,Sheet1!A:A,A2,Sheet1!D:D,1)

 

[felixstraube]

You could use this and avoid the visible column:

Book1
	A	B
1	Order number	Shipped
2	1538930	850
3	1539857	54
4	1540524	54
5	1540529	69
6	1542289	13
7	1556328	329
Sheet2

Cell Formulas
Range	Formula
B2:B7	B2	=SUM(IFERROR((Sheet1!$A$2:$A$10000=Sheet2!A2)*Sheet1!$C$2:$C$10000*SUBTOTAL(103,OFFSET(Sheet1!$C$1,SEQUENCE(COUNT(Sheet1!$C$2:$C$10000)),0)), 0))

 

[Fluff]

Personally I'd use the helper column, rather than volatile functions.

 

[felixstraube]

Sure, if you have a big number of rows, offset would degrade performance.