Sumif Two workbooks with additional Criteria

**ili_Sophia** · 09-03-2019, 09:27 AM

Hi there,

I have 2 excel workbook, Buyer example and Shipment Data.

in the Buyer Example workb, there are the fields of TR number, buyer, buyer number and Cost.
In the Shipment data wb, there are the buyer, buyer number and shipment amount.

The shipment data has a long list of shipment amount that needs to be sum up and added into the buyer example.

1. I have to match the buyer and buyer number in the shipment data with the Buyer example to calculate the total shipment amount that needs to be added into the buyer example wb
2. However there is a criteria to the amount that should be calculated in the shipment amount of the buyer example wb.
3. The criteria is that the Shipment amount should be 60% more than the Cost for it to be considered paid.
4. The Buyer number can appear in multiple TR number. hence once the shipment amount has reached 60% more than the cost, the shipment amount will be carry forward to the next line that matches the buyer and buyer number.

i have considered using sumif, but i am not sure how to add in the criteria of the 60%

I have attached two workbooks for the example. in the buyer example wb, i have added the final product that the excel should produce.

**Richard Buttrey** · 09-03-2019, 10:09 AM

If I've understood correctly then with both workbooks open enter the following in F2 of the Buyer workbook and copy it down

FORMULA]=IF(SUMIFS('[Shipment data.xlsx]Sheet1'!$C$2:$C$16,'[Shipment data.xlsx]Sheet1'!$B$2:$B$16,C2)>D2*1.6,"Paid","")[/FORMULA]
Similarly in E2

Formula:

Please Login or Register  to view this content.

Obviously you should create dynamic range names for columns B & C of the shipment data so that you can use the range name rather than the C2:C16 and B2:B16 cell refs in the formula.

Is there a reason why you separate these workbooks. Can't you have the Shipment Data as another sheet in the Buyer workbook?

**ili_Sophia** · 09-03-2019, 10:25 AM

Originally Posted by Richard Buttrey

If I've understood correctly then with both workbooks open enter the following in F2 of the Buyer workbook and copy it down

FORMULA]=IF(SUMIFS('[Shipment data.xlsx]Sheet1'!$C$2:$C$16,'[Shipment data.xlsx]Sheet1'!$B$2:$B$16,C2)>D2*1.6,"Paid","")[/FORMULA]
Similarly in E2

Formula:

Please Login or Register  to view this content.

Obviously you should create dynamic range names for columns B & C of the shipment data so that you can use the range name rather than the C2:C16 and B2:B16 cell refs in the formula.

Is there a reason why you separate these workbooks. Can't you have the Shipment Data as another sheet in the Buyer workbook?

Hi Richard,
Thanks for The reply. I cannot combine into one workbook as they are done by different departments and there are other data/worksheets in their respective workbook.

Hence, is it possible to code the sum if into vba where only buyer example workbook is open?

Also how do I solve point 4. If the shipment amount has reached 60% more than the cost, the balance shipment amount will be carried over to the next like that matches the buyer and buyer no.

**Richard Buttrey** · 09-03-2019, 11:44 AM

Hi,

Does using this formula in a new column F (after shifting the last one across one column)

Formula:

Please Login or Register  to view this content.

If not please supply a worked example where you have manually entered some typical results that you expect.

Sumif Two workbooks with additional Criteria

LinkBack

Thread Tools

Rate This Thread

Display

Sumif Two workbooks with additional Criteria

Re: Sumif Two workbooks with additional Criteria

Re: Sumif Two workbooks with additional Criteria

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] I need to add additional criteria to an array Sumif that counts individual values

HELP! SUMIF with additional criteria required...

[SOLVED] Sumif between a range of dates plus one additional criteria

[SOLVED] SUMIF by date and one additional criteria

how to add additional criteria to a SUMIF formual?

Sumproduct/sumif/indirect across multiple workbooks, worksheets, multiple criteria

Excel 2003 Additional Workbooks

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions