Sumifs when there are several line items

Hi there,

I'm stuck and could really use some assistance on this.

I am currently trying to sum between workbooks (for comparative purposes) using the same invoice number as criteria. This typically is not an issue, however the workbook I am writing the formula into has the invoice broken down into multiple line items, whereas the source workbook I am pulling the data from does not. For example:

Formula workbook:
Invoice 1 50
Invoice 1 100
Invoice 1 850

Source workbook:
Invoice 1 1,000

Therefore every line with the sumif in the formula workbook returns 1,000 (total 3,000 for Invoice 1 in example above) which is obviously incorrect.

What I am after is a formula, which when there are more than 1 line item for the same invoice (duplicates, as above), it will return the whole amount from the source workbook, and any future sums for the same invoice number will return zero. This will avoid getting 3,000 like in the example above.

For example:

Formula workbook:
Invoice 1 50 1,000
Invoice 1 150 0
Invoice 1 850 0

Is it possible to sumif the first criteria and when there are duplicates of the same criteria this returns nil? Once I can obtain the 1,000 I will add a column to split the 1,000 per the invoice percentages.

Below is what I currently have:
=IFERROR(IF(COUNTIF(AG:AG,AG106686)>1,IF(RIGHT(V106686,8)="Variance",0,IF(AC106686=0,0,SUMIF('Vendor Arrivals Pivot'!$H$2:$H$3974,'Combined VAT Report'!AG106686,'Vendor Arrivals Pivot'!$G$2:$G$3975)))*AC106686/IF(RIGHT(V106686,8)="Variance",0,IF(AC106686=0,0,SUMIF('Vendor Arrivals Pivot'!$H$2:$H$3974,'Combined VAT Report'!AG106686,'Vendor Arrivals Pivot'!$G$2:$G$3975))),IF(RIGHT(V106686,8)="Variance",0,IF(AC106686=0,0,SUMIF('Vendor Arrivals Pivot'!$H$2:$H$3974,'Combined VAT Report'!AG106686,'Vendor Arrivals Pivot'!$G$2:$G$3975)))),0)

This works to the point that it will split the amount in the source workbook as a percentage of the line item. However, I have seen there are some cases where the amounts in the formula workbook for the same invoice are incorrect, and this creates an error.

The end product would ideally be, the returned invoice value from the source workbook, which when dragged down would return nil for any other instances of that invoice.

Unfortunately due to the nature of the formula workbook, I cannot remove duplicates, as they offer detail which is needed. I can also not upload any workbooks as this is sensitive data.

I hope this is enough of an explanation for you.

Many thanks in advance,
Alex

Dummy workbook.xlsx Workbook added

Originally Posted by oeldere

E2 =if(countif($A$2:$A2,$A2)=1,VLookup($A2,'Source Data'!$A$2:$B$4,2,0),0) and drag down.

Thank you for your reply. However the source and formula data aren't in order so the vlookup doesn't return the correct data. Is there a way to use sumif as an alternative?

Regards

Originally Posted by oeldere

g2 =if(countif($A$2:$A2,$A2)=1,sumif('Source Data'!$A$2:$A$4,'Formula Sheet'!A2,'Source Data'!$B$2:$B$4),0) and drag down.

This is fantastic, exactly what I needed. Thank you so much