Sum cell dependant on Ratio with XLOOKUP and SUMIF

[dharvey1978]

Hi All,

I am after your help with an issue I am having I am trying to sum cells in columns B12:T18 that is sum by a particular row in this case row B11:T11 this row has ratios in it eg: 1:1, 1:2, 1:3.

So for instance the cell in B12 I want that cell to be if cell B1 = A23:A142 (2:3) then if true, Sum B23:B142 (2) multiply cell B2 divided by C23:C142 (3) in relation to the corresponding ratio.

The ratios in cells B1:T1 won't always be the same.

I have tried using the Xlookup with both the Sum and Sumif formulas and appears not to be working for me. I have attached a sample workbook as well

Thanks

[Glenn Kennedy]

I do not understand your expected answers, or your explanation. So at a guess:

=IF(B2="","",B2*VLOOKUP(B$1,Table1,2,FALSE)/VLOOKUP(B$1,Table1,3,FALSE))

copied across and dnown.

[dharvey1978]

Thanks for your help Glenn that has worked.

[Gregb11]

As an alternative, this option does not require the ratio table at all. You can change the ratio row 11 (or row 1) to whatever you want and it should return the right value. Since I don't know the full use of your spreadsheet, I don't know if this is viable or not, but just thought I'd add as an alternative solution:

=IF(B2="","",B2/RIGHT(B$11,LEN(B$11)-FIND(":",B$11))*LEFT(B$11,FIND(":",B$11)-1))

(You can change the B$11 Reference to B$1 if that makes more sense.)

Also, since you're using 365, you could use just 1 formula in 1 cell to get the whole grid (No need to copy down or across as it will SPILL into the other cells). In B12, enter:

=IF(B2:T8="","",B2:T8/RIGHT(B$11:T$11,LEN(B$11:T$11)-FIND(":",B$11:T$11))*LEFT(B$11:T$11,FIND(":",B$11:T$11)-1))