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.
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'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?
HansVogelaar I've quickly too my file and removed the sensitive information and relabeled the items so that you see the table I'm working with. I also gave an example of how I wanted the percentages calculated so put one in there for your reference.
Hope this helps, and thank you for your reply. Much appreciate the help.
Thank you.Toots.
In E4:
=IFERROR($D4/SUMPRODUCT(SUBTOTAL(109,OFFSET($D$4,ROW($D$4:$D$86)-ROW($D$4),0)),($A$4:$A$86=$A4)*($B$4:$B$86=$B4)),"")
If you don't have Microsoft 365 or Office 2021, you may have to confirm the formula with Ctrl+Shift+Enter.
Fill down.