Help! Multiple Criteria

[Ryske]

Hello everyone, hope all is well. I will be making a presentation – automated gathering of info. I just can’t figure out how to make this happen.
The first spreadsheet would be the output, the date on the top are the ship dates. I have customers that are ordering 2 items irregularly, need to track their orders.

[TABLE="width: 994"]
<colgroup><col><col span="14"></colgroup><tbody>[TR]
[TD][/TD]
[TD="colspan: 2"]1-Jan-17[/TD]
[TD="colspan: 2"]2-Jan-17[/TD]
[TD="colspan: 2"]3-Jan-17[/TD]
[TD="colspan: 2"]4-Jan-17[/TD]
[TD="colspan: 2"]5-Jan-17[/TD]
[TD="colspan: 2"]6-Jan-17[/TD]
[TD="colspan: 2"]7-Jan-17[/TD]
[/TR]
[TR]
[TD]Customer[/TD]
[TD]Item 1[/TD]
[TD]Item 2[/TD]
[TD]Item 1[/TD]
[TD]Item 2[/TD]
[TD]Item 1[/TD]
[TD]Item 2[/TD]
[TD]Item 1[/TD]
[TD]Item 2[/TD]
[TD]Item 1[/TD]
[TD]Item 2[/TD]
[TD]Item 1[/TD]
[TD]Item 2[/TD]
[TD]Item 1[/TD]
[TD]Item 2[/TD]
[/TR]
[TR]
[TD]Company A[/TD]
[TD]10[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]Company B[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]20[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Company C[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
</tbody>[/TABLE]

Second spreadsheet is the raw data:


[TABLE="width: 327"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Customer[/TD]
[TD]Item[/TD]
[TD]Ship date[/TD]
[TD] Quantity[/TD]
[/TR]
[TR]
[TD]Company A[/TD]
[TD]Item 1[/TD]
[TD="align: right"]1-Jan-17[/TD]
[TD] 10[/TD]
[/TR]
[TR]
[TD]Company B[/TD]
[TD]Item 2[/TD]
[TD="align: right"]3-Jan-17[/TD]
[TD] 20[/TD]
[/TR]
[TR]
[TD]Company C[/TD]
[TD]Item 1[/TD]
[TD="align: right"]5-Jan-17[/TD]
[TD] 20[/TD]
[/TR]
[TR]
[TD]Company C[/TD]
[TD]Item 2[/TD]
[TD="align: right"]5-Jan-17[/TD]
[TD] 20[/TD]
[/TR]
[TR]
[TD]Company A[/TD]
[TD]Item 2[/TD]
[TD="align: right"]7-Jan-17[/TD]
[TD] 50[/TD]
[/TR]
</tbody>[/TABLE]


Is there a simple formula for this? Any help would be greatly appreciated. Thanks in advance!

 

[Jeffrey Mahoney]

A SUMIFS formula will do. Assume "Customer" is in cell A2 for the top table. Assume the bottom table is on sheet4. Do in this order...

Cell B3 formula: =SUMIFS(Sheet4!$D$1:$D$6,Sheet4!$A$1:$A$6,Sheet3!$A3,Sheet4!$C$1:$C$6,Sheet3!B$1,Sheet4!$B$1:$B$6,Sheet3!B$2)
Cell C3 formula: =SUMIFS(Sheet4!$D$1:$D$6,Sheet4!$A$1:$A$6,Sheet3!$A3,Sheet4!$C$1:$C$6,Sheet3!B$1,Sheet4!$B$1:$B$6,Sheet3!C$2)

Copy those two formulas down to the next rows

Copy those two columns of formulas and paste into Cells D3, F3, H3, J3, L3, and N3

TA DA

 

[Ryske]

Hi Jeffrey, thanks for the response. I understand now how the sumifs works, however, the dates top of the first image are merged cells for example "1-Jan-2017 is located in B1:C1". My apologies if I missed this on my first post. I cannot attach the actual spreadsheets I am working on, my access on this is disabled. Thank you!