Forum Discussion

SeanM256's avatar
SeanM256
Copper Contributor
Jun 17, 2020

Consolidating a SUMIF Function + an IF Statement

Hello All,    So I have a table which is doing a series of calculations based off of different language criteria.  I want to add an additional variable which will only run this string of calculatio...
excel
Formulas and Functions
Subodh_Tiwari_sktneer's avatar
Subodh_Tiwari_sktneer
Silver Contributor
Jun 17, 2020

SeanM256 

Are you copying the formula down the rows and you want the formula to look at the column F values on Inventory Tracking Sheet and if the column F contains "Bestobell" in a row, you want it to calculate the SUMIFS for that row else return a 0 and move to the next row?

 

If that's the case, you may try this...

 

=IF('Inventory Tracking'!F4 = "Bestobell",SUMIFS('Inventory Tracking'!$K$4:$K$10004, 'Inventory Tracking'!$G$4:$G$10004, "Received", 'Inventory Tracking'!$I$4:$I$10004, C11)+SUMIFS('Inventory Tracking'!$M$4:$M$10004, 'Inventory Tracking'!$G$4:$G$10004, "B/O Received", 'Inventory Tracking'!$I$4:$I$10004, C11)-SUMIFS('Inventory Tracking'!$N$4:$N$10004, 'Inventory Tracking'!$G$4:$G$10004, "Issued", 'Inventory Tracking'!$I$4:$I$10004, C11)-SUMIFS('Inventory Tracking'!O61:O10009, 'Inventory Tracking'!G61:G10009, "Returned", 'Inventory Tracking'!I61:I10009, C11), 0)

 

The above formula looks at the cell F4 on Inventory Tracking' Sheet and if F4 is equal to "Bestobell", it would calculate the SUMIFS else the formula would return a 0.

 

And if you copy the above formula down one row, the new formula would look at the cell F5 on Inventory Tracking' Sheet and check the value in F5 and would calculate as per the logic.

SeanM256's avatar
SeanM256
Copper Contributor
Jun 17, 2020

Subodh_Tiwari_sktneer 

 

So this function is applied to the TrackingSheet2 (see .jpg) - In the column "Quantity in Stock".  This formula is copied to all of the cells in Quantity in Stock.  It reads the information highlighted on TrackingSheet1 (see .jpg) and tells me how much stock for each material (Sub-Category) is available. Currently, this is done via the string of SUMIF functions.  

 

The goal is to add the variable to this string for 'TrackingSheet2' so I can collect inventory data for two separate buildings, in the same spreadsheet.  

 

So to answer, yes the formula is copied, but the cell value that changes is the 'C' column value in the function, the 'F' column value needs to read from the whole column on 'TrackingSheet1'. 

 

I hope that clarifies any questions.

 

Thanks again,

 

Sean

 

Resources