Forum Discussion
Consolidating a SUMIF Function + an IF Statement
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)
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.
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.
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.
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.