Consolidating a SUMIF Function + an IF Statement

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

I want to combine this 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 IF Statement:

IF(AND'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. 

Any help is appreciated! 

Cheers, 

Sean 

[#2]

@SeanM256 

How would you translate the IF condition in words?

If it is like...

If there is any cell with the text "Bestobell" in the range 'Inventory Tracking'!$F$4:$F$10004 then calculate the SUMIFS else return 0 in the formula cell, you may try the below formula.

If that's not the case, please elaborate your IF condition in words.

=IF(COUNTIF('Inventory Tracking'!$F$4:$F$10004, "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)

[#3]

Thank you for you time.

"If there is any cell with the text "Bestobell" in the range 'Inventory Tracking'!$F$4:$F$10004 then calculate the SUMIFS else return 0 in the formula cell" - Would be the correct word description of what I would like to achieve.  

The function you gave gives the correct value, but doesn't appear to distinguish between the text variable "Bestobell" and other text, if i change that text variable it will still do the SUMIFS for both text types.

@Subodh_Tiwari_sktneer 

[#4]

@SeanM256 

As per the IF condition, if there is even a single occurrence of the text "Bestobell" in the range 'Inventory Tracking'!$F$4:$F$10004, the SUMIFS would be calculated else it would return 0 in the formula cell.

So the logical condition COUNTIF('Inventory Tracking'!$F$4:$F$10004, "Bestobell") is counting the number of times the text string "Bestobell" in the range 'Inventory Tracking'!$F$4:$F$10004 and if even a single occurrence of "Bestobell" is found, the SUMIFS would be calculated.

[#5]

@Subodh_Tiwari_sktneer 

Understood, so I only want it to run the SUMIFS when the "Bestobell" Text is present. If that value isn't present, I don't want it to run that function. 

So the issue is that if I have two different texts aka "Bestobell" and "EastMall", and as per the function below it is running the string because it sees one iteration of Bestobell, but is not excluding any other factors (aka any lines that have EastMall).

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

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

The COUNTIF function doesn't appear to achieve that goal. 

[#6]

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

[#7]

@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