How to calculate a value based on multiple criteria

[GrumpyChi]

Hi everyone,

I have a data set which as an example has the below information in it


Invoice number / charge type (FULL or DISCOUNT) / Total Volume / Total Charge
1461615 FULL 100 50
1461615 DISCOUNT 100 -10
252656 FULL 150 21

So for invoice 1461615 on the discount line, I need to calculate the unit cost which is the total charge divided by the total volume but using the FULL line

I can't figure out how to do this. I've tried SUMPRODUCT but it's returning the value for the discount line unit cost which is not what I need

Thank you

 

[GrumpyChi]

Hi Fluff, I am getting an error of the first argument of LET must be a valid name

 

[Fluff]

Are you using an English language version of Excel?

 

[GrumpyChi]

Yes an English language version.

 

[Fluff]

In that case I don't see why you get hat error, try changing it like

=LET(xyz,XMATCH(A2,A$1:A1,0,-1),IF(B2="discount",INDEX(E$1:E1,xyz)/INDEX(D$1:D1,xyz),""))