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

excel
Formulas and Functions

6 Replies

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

         

         

Resources