Jun 06 2021 12:50 PM
Jun 06 2021 01:15 PM
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)
Jun 06 2021 01:23 PM
Jun 06 2021 01:54 PM
SolutionAll 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.
Jun 06 2021 02:00 PM
Jun 06 2021 02:08 PM
You omitted the comma between A28:A32 and "Agent 1", and also between A28:A32 and "Agent 3".
Jun 06 2021 02:13 PM
Mar 01 2022 06:57 PM
Mar 02 2022 03:55 AM
Could you attach a sample workbook, or make it available through OneDrive, Google Drive, Dropbox or similar?
Mar 02 2022 04:38 AM
@Hans Vogelaar 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.
Mar 02 2022 05:31 AM
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.
Mar 02 2022 05:47 AM
Mar 02 2022 06:40 PM
Mar 03 2022 02:24 AM
See the documentation for the SUBTOTAL function.
Function_num |
Function_num |
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 |