Combining IF & SUMIF Functions

[seanm256]

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 calculations if another text variable is present in a different cell. If that text variable isn't present, I want it to either present a value of 0, or simply not run the string of calculations.

The idea was to have a function that I could replace the text criteria and have it distinguish between two factors.

Type A - Run SUMIFS when "Bestobell" is present only.
Type B - Run SUMIFS when "EastMall" is present only.

Reference Images:

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. 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'.


Base Function:

=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)

With an potential IF Statement:

IF('Inventory Tracking'!$F$4:$F$10004= "Bestobell", (INSERT SUMIF ABOVE) , 0 OR "")



Obviously in this structure I cannot do that, but I was hoping maybe there is a guru out there who might be able to help me condense the formula to allow it to work, or have a better method to produce the same effect.


Any help is appreciated! Let me know if you have any questions!

Cheers,

Sean

 

[seanm256]

As always great advice. I have a series of hidden columns that I realistically should have checked. There was a #REF error in one of the more recent entries. I think due to incorrect data input. So it was causing the functions to error out. I will have to remember to use F9 more frequently for auditing my excel sheets. Thanks again.

Regards,

Sean

 

[KRice]

Thanks for the update...I'm glad you found the issue and got it working.