Forum Discussion

MrGTH's avatar
MrGTH
Copper Contributor
Jun 06, 2021

Calculate percent of total with two criteria in one operation

I'm having trouble thinking of a way to solve this in one single operation. The exact statement is "calculate the percentual part of Agent 1 and Agent 3" compared to the amount of surveys". I can think of calculating the total for all surveys in one cell using sum, and then using another cell to calculate the sun of agents 1 and 2, AND THEN using another cell to do amount/total, where total is the named cell where I have the total of surveys.

There must be a better way to do this, right? Maybe with vlookup? I'm an intermediate Excel user at best
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Jun 06, 2021

    MrGTH 

    All three formulas should produce the same result. With the data from your photo, I get this:

     

     

    The formula VLOOKUP("Agent 1",A28:B32,2,FALSE) does the following:

    It looks for the value "Agent 1" in the first column of the range A28:B32, i.e. in A28:A32.

    If found, it returns the corresponding value from the 2nd column of A28:B32, i.e. from B28:B32.

    The last argument FALSE tells Excel to look for an exact match.

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jun 06, 2021

    MrGTH 

    A simple option:

     

    =SUM(B28,B30)/SUM(B28:B32)

     

    Format the cell with the formula as a percentage.

     

    More sophisticated:

     

    =(SUMIF(A28:A32,"Agent 1",B28:B32)+SUMIF(A28:A32,"Agent 3",B28:B32))/SUM(B28:B32)

     

    or

     

    =(VLOOKUP("Agent 1",A28:B32,2,FALSE)+VLOOKUP("Agent 3",A28:B32,2,FALSE))/SUM(B28:B32)

    • MrGTH's avatar
      MrGTH
      Copper Contributor
      Jun 06, 2021
      Great, thank you so much!
      I don't understand some of the Vlookup logic, like the False statement or the number 2 after the B range, because I'm not familiar with Vlookup. Something I need to learn.

      However I do understand the SUMIF operation, but it's giving me 340.16% for some reason
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Jun 06, 2021

        MrGTH 

        All three formulas should produce the same result. With the data from your photo, I get this:

         

         

        The formula VLOOKUP("Agent 1",A28:B32,2,FALSE) does the following:

        It looks for the value "Agent 1" in the first column of the range A28:B32, i.e. in A28:A32.

        If found, it returns the corresponding value from the 2nd column of A28:B32, i.e. from B28:B32.

        The last argument FALSE tells Excel to look for an exact match.

Resources