Nested SUMIFS each with multiple criteria

Joseph Adams · 04/23/21

Hello,

Diving right in to the problem at hand. I have one sheet that handles reagent requests from multiple departments and another sheet that contains all of the stock solutions these reagent requests are made from. I want successfully created a formula that works for 1 sumifs scenario.

=[Request Vol (ml)]@row - SUMIFS({Total Vol - Queue}, {Buffer Name - Queue}, "1X FC Wash", {Buffer Lot - Queue}, [Stock Buffer Lot]@row) / 10

Where:

Request Vol (ml)]@row = Total volume of 10X stock solution

{Total Vol - Queue} = Volume of 1X solution

{Buffer Name - Queue} = Name of 1X solution

{Buffer Lot - Queue} = Lot of the 1X solution that needs to match the 10X stock solution which is [Stock Buffer Lot]@row

This formula will subtract the volume of the requested reagent (buffer) from the stock solution if

The requested buffer is "1X FC Wash"
The Buffer Lot of the requested buffer matches the Stock Buffer lot

I divide by 10 because the stock solutions are 10X more concentrated. This formula works just fine and lets me know how much of my remaining stock solution I have of 10X FC Wash. However, I now need to add additional stock solutions to this sheet and have not found a way to do without errors.

Here is what I want the formula to do:

If the stock solution is 10X wash buffer, then sum all of the 1X Wash buffers from a separate sheet with the same lot as the 10X Wash buffer and then subtract from the total 10X wash buffer @row. If the stock solution is 10X FC Depro then sum all of the 1X FC Depro buffers from a separate sheet with the same lot as the 10X FC Depro and then subtract from the total 10X wash buffer @ row.

I have a total of 5 stock solutions that I need to string together in this formula. Any help would be appreciated!

Genevieve P. · 04/27/21

Hi @Joseph Adams

In your current sheet with the formula, where are you indicating what the current stock solution is? You note, "if the stock solution is 10x Wash Buffer, then do this, if it's 10X FC Depro, then do this". Where are these values located?

Currently in your formula you're manually typing in "1x FC Wash" as the criteria for the {Buffer Name - Queue} reference. I'm wondering if it would be easier to have a variable in there which is based on a column in your current sheet.

You could have a helper column that looks at your Stock Solution column (where I presume you have 10x Wash Buffer, 10X FC Depro, etc listed), and return the correct text to search for. Then your formula can reference the [Helper Column]@row instead of "typing" the value, and it will adjust per-row depending on your Stock Solution. Does that make sense?

Here's an example of what that formula may look like:

=IF([Stock Solution]@row = "10X wash buffer", "1X Wash", IF([Stock Solution]@row = "10X FC Depro", "10X FC Depro"))

Then your formula would stay the exact same, you would just reference this formula as your criteria:

=[Request Vol (ml)]@row - SUMIFS({Total Vol - Queue}, {Buffer Name - Queue}, [Helper Column]@row, {Buffer Lot - Queue}, [Stock Buffer Lot]@row) / 10

Let me know if I've misunderstood your set-up! In this instance it may be helpful to see screen captures of both of your sheets & columns, but please block out any sensitive data.

Cheers,

Genevieve

Joseph Adams · 04/27/21

Hello Genevieve,

This is actually a great idea that I can use elsewhere to clean up some of my longer formulas but not this one unfortunately. I have different modifiers I need to do for each one (i.e it wont always be "/10"). I had a couple other issues I had to sort out after I posted this since we added some additional buffers but I have it working now. Below is how I added all of the sumif scenarios together into one string to subtract from Stock solution of 10% Triton:

IF([Buffer Name]@row = "10% Triton X", [Request Vol (ml)]@row -

(SUMIFS({Total Vol - Queue}, {Buffer Name - Queue}, "0.5% Triton X", {Triton Lot - Queue}, [Stock Buffer Lot]@row) / 20 +

SUMIFS({Total Vol - Queue}, {Buffer Name - Queue}, "FC Incorporation", {Triton Lot - Queue}, [Stock Buffer Lot]@row) / 100 +

SUMIFS({Total Vol - Queue}, {Buffer Name - Queue}, "Quench", {Triton Lot - Queue}, [Stock Buffer Lot]@row) / 400 +

SUMIFS([Request Vol (ml)]:[Request Vol (ml)], [Buffer Name]:[Buffer Name], "10X Plate Wash", [10% Triton Stock Lot]:[10% Triton Stock Lot], [Stock Buffer Lot]@row) / 40 +

SUMIFS([Request Vol (ml)]:[Request Vol (ml)], [Buffer Name]:[Buffer Name], "Flow cell deprotection", [10% Triton Stock Lot]:[10% Triton Stock Lot], [Stock Buffer Lot]@row) / 400 +

SUMIFS({Total Vol - Queue}, {Buffer Name - Queue}, "FC deprotection", {Triton Lot - Queue}, [Stock Buffer Lot]@row) / 400 +

SUMIFS([Request Vol (ml)]:[Request Vol (ml)], [Buffer Name]:[Buffer Name], "10X FC Wash", [10% Triton Stock Lot]:[10% Triton Stock Lot], [Stock Buffer Lot]@row) / 40 +

SUMIFS({Total Vol - Queue}, {Buffer Name - Queue}, "Plate Incorporation", {Triton Lot - Queue}, [Stock Buffer Lot]@row) / 100)

As you can see it is quite long but I got it to work :) Some of the solutions that contain 10% triton are stock solutions themselves and exist on the same sheet where the other solutions are on a different sheet. I removed a couple more sensitive buffers but this was how I was able to insert all of the sumifs. I did however use your tip on another sheet like I said and it made my long formula into a 2 line one. Thanks so much!

Genevieve P. · 04/27/21