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 

 

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)
  • SeanM256's avatar
    SeanM256
    Copper Contributor
    Jun 17, 2020

    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 

    • Subodh_Tiwari_sktneer's avatar
      Subodh_Tiwari_sktneer
      Silver Contributor
      Jun 17, 2020

      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.

       

       

      • SeanM256's avatar
        SeanM256
        Copper Contributor
        Jun 17, 2020

        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. 

Resources