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.
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.
See the documentation for the SUBTOTAL function.
Function_num
(includes hidden rows)Function_num
(ignores hidden rows)Function
1
101
AVERAGE
2
102
COUNT
3
103
COUNTA
4
104
MAX
5
105
MIN
6
106
PRODUCT
7
107
STDEV
8
108
STDEVP
9
109
SUM
10
110
VAR
11
111
VARP
- I'm looking at the formula and don't know where the value 109 comes from. How was this 109 derived? I want to also apply the formula to another table similar but up a level (no longer looking at size but just model and build.
Thank you. - Thank you. It works!
I'll study the formula to get a better understanding of how it calculates.