Forum Discussion
Calculate percent of total with two criteria in one operation
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.
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)
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
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".
- Hi,
I'm also looking to calculate the percentage with more than 1 criteria. The formula isn't working for me. I think I am missing an additional condition to get the result I need.
My sample (My table is a pivot table)
FILTER: ALL Customers (option to select individual customers to see change in %)
MODEL BUILD SIZE QTY QTY %
Model 1 Build A SM 5
Model 1 Build B MD 3
Model 2 Build A SM 2
Model 2 Build B MD 3
Model 2 Build C LG 2
Model 3 Build A SM 10
Model 3 Build B MD 4
Results looking to achieve: QTY % for MODEL + BUILD + SIZE
Thank you.Could you attach a sample workbook, or make it available through OneDrive, Google Drive, Dropbox or similar?
