SOLVED

Calculate percent of total with two criteria in one operation

%3CLINGO-SUB%20id%3D%22lingo-sub-2420283%22%20slang%3D%22en-US%22%3ECalculate%20percent%20of%20total%20with%20two%20criteria%20in%20one%20operation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2420283%22%20slang%3D%22en-US%22%3EI'm%20having%20trouble%20thinking%20of%20a%20way%20to%20solve%20this%20in%20one%20single%20operation.%20The%20exact%20statement%20is%20%22calculate%20the%20percentual%20part%20of%20Agent%201%20and%20Agent%203%22%20compared%20to%20the%20amount%20of%20surveys%22.%20I%20can%20think%20of%20calculating%20the%20total%20for%20all%20surveys%20in%20one%20cell%20using%20sum%2C%20and%20then%20using%20another%20cell%20to%20calculate%20the%20sun%20of%20agents%201%20and%202%2C%20AND%20THEN%20using%20another%20cell%20to%20do%20amount%2Ftotal%2C%20where%20total%20is%20the%20named%20cell%20where%20I%20have%20the%20total%20of%20surveys.%3CBR%20%2F%3E%3CBR%20%2F%3EThere%20must%20be%20a%20better%20way%20to%20do%20this%2C%20right%3F%20Maybe%20with%20vlookup%3F%20I'm%20an%20intermediate%20Excel%20user%20at%20best%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2420283%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2420332%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20percent%20of%20total%20with%20two%20criteria%20in%20one%20operation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2420332%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1072303%22%20target%3D%22_blank%22%3E%40MrGTH%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EA%20simple%20option%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUM(B28%2CB30)%2FSUM(B28%3AB32)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFormat%20the%20cell%20with%20the%20formula%20as%20a%20percentage.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMore%20sophisticated%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3D(SUMIF(A28%3AA32%2C%22Agent%201%22%2CB28%3AB32)%2BSUMIF(A28%3AA32%2C%22Agent%203%22%2CB28%3AB32))%2FSUM(B28%3AB32)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eor%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3D(VLOOKUP(%22Agent%201%22%2CA28%3AB32%2C2%2CFALSE)%2BVLOOKUP(%22Agent%203%22%2CA28%3AB32%2C2%2CFALSE))%2FSUM(B28%3AB32)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2420335%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20percent%20of%20total%20with%20two%20criteria%20in%20one%20operation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2420335%22%20slang%3D%22en-US%22%3EGreat%2C%20thank%20you%20so%20much!%3CBR%20%2F%3EI%20don't%20understand%20some%20of%20the%20Vlookup%20logic%2C%20like%20the%20False%20statement%20or%20the%20number%202%20after%20the%20B%20range%2C%20because%20I'm%20not%20familiar%20with%20Vlookup.%20Something%20I%20need%20to%20learn.%3CBR%20%2F%3E%3CBR%20%2F%3EHowever%20I%20do%20understand%20the%20SUMIF%20operation%2C%20but%20it's%20giving%20me%20340.16%25%20for%20some%20reason%3C%2FLINGO-BODY%3E
New Contributor
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
6 Replies

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

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
best response confirmed by MrGTH (New Contributor)
Solution

@MrGTH 

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

 

S0484.png

 

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.

I tried the SUMIF formula again and Now I just get error. I've double checked to make sure I'm doing it right, but I can't find my mistake. It must be some dumb little mistake I'm making

@MrGTH 

You omitted the comma between A28:A32 and "Agent 1", and also between A28:A32 and "Agent 3".

Thank you so much. That was the mistake indeed. I tried it now and it gave me the correct result!