Calculate percent of total with two criteria in one operation

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


A simple option:




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)




=(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)


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.

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


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!