Perform operations @row then SUM together

Joseph Adams

Hello All,

I am trying to right a simplified formula to do a complicated task and having difficulty. Below is the formula in question. I am trying to SUM up all volumes of some buffers that use a specific lot of 3M KCL. I also created a helper column with a factor to divide the total by based on which buffer is using the 3M KCl since they also use different amounts of the stock solution.

=IF([Buffer Name]@row = "3M KCl", [Request Vol (ml)]@row - (SUMIFS({Total Vol - Queue}, {3M KCl Helper}, "<>", {Buffer Lot - Queue}, [Stock Buffer Lot]@row)) / {3M KCl Helper}(@cell))

=IF([Buffer Name]@row = "3M KCl" checking for 3M KCl in current sheet so it will only perform calculation if this is true

[Request Vol (ml)]@row total volume of stock buffer.

- (SUMIFS({Total Vol - Queue} subtracting the total volume of buffer found on separate sheet that meet the below criteria. This volume will then be modified by dividing it by the Helper value. For example, lets say the stock solution is 10X and the buffer that uses the 10X is only 1X. I then need to subtract the total of 1X buffer/divided by 10 to calculate the correct remaining stock buffer. Example:

1000ml of 10X stock

I need 1000ml of 1X buffer. I would take 1000/10 to get 100ml of needed stock to make the 1X solution so the remaining stock is 1000 - (1000/10) = 900

The number I need to divide by depends on the buffer I need to make; hence the helper column.

{3M KCl Helper}, "<>", only sums when the helper column is NOT blank. Helper column is on separate sheet, same sheet as the [Total Vol- Queue].

{Buffer Lot - Queue}, [Stock Buffer Lot]@row)) 2nd criteria. Only performs sumif when the lot recorded on Queue sheet matches the Stock Buffer lot @row where formula is on. The {Buffer Lot - Queue} is referencing multiple columns. 4 columns to be specific. The lot number can only exist in one of them however. I think this reference is causing some of the issues since it is not a single column.

/ {3M KCl Helper}(@cell)) divides the summed total by the helper value at the row where the stock lot was found. I have formulas on the this sheet that do this divide function but the value is always 10 so it didnt have to reference another sheet to get the divisor like I do for this one.

Any help would be appreciated!

L_123

"The {Buffer Lot - Queue} is referencing multiple columns. 4 columns to be specific."

Each range reference in a sumifs must contain the same number of lookup fields. If you have one referencing 4 columns, and others referencing only one, then it isn't going to work. You're probably going to have to stack your sumifs and add them together.

Joseph Adams

AH, that makes sense. Thanks for the assist

Joseph Adams

I fixed the first issue by adding all of my individual SUMIF strings together but I am still having an issue with the last bit where I am trying to divide the volume total by the value found in the Helper column

/ {3M KCl Helper}(@cell))

I am getting an #UNPARSEABLE error. Inputting a number in place of the above sheet reference works when testing , as well as referencing a column on the sheet with the formula in place of the cross sheet reference. The issue I think is with referencing another sheet column like I do here.