Calculate percent of total with two criteria in one operation

[MrGTH]

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

[#2]

@MrGTH 

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)

[#3]

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

[#4 · Accepted solution]

@MrGTH 

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.

[#5]

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

[#6]

@MrGTH 

You omitted the comma between A28:A32 and "Agent 1", and also between A28:A32 and "Agent 3".

[#7]

Thank you so much. That was the mistake indeed. I tried it now and it gave me the correct result!

[#8]

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.

[#9]

@Toots912 

Could you attach a sample workbook, or make it available through OneDrive, Google Drive, Dropbox or similar?

[#10]

@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.

[#11]

@Toots912 

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.

[#12]

Thank you. It works!
I'll study the formula to get a better understanding of how it calculates.

[#13]

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.

[#14]

@Toots912 

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