SOLVED

New Contributor

# Calculate percent of total with two criteria in one operation

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
13 Replies

# Re: Calculate percent of total with two criteria in one operation

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)

# Re: Calculate percent of total with two criteria in one operation

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
best response confirmed by MrGTH (New Contributor)
Solution

# Re: 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.

# Re: Calculate percent of total with two criteria in one operation

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

# Re: Calculate percent of total with two criteria in one operation

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

# Re: Calculate percent of total with two criteria in one operation

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

# Re: Calculate percent of total with two criteria in one operation

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.

# Re: Calculate percent of total with two criteria in one operation

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

# Re: Calculate percent of total with two criteria in one operation

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

# Re: Calculate percent of total with two criteria in one operation

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.

# Re: Calculate percent of total with two criteria in one operation

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

# Re: Calculate percent of total with two criteria in one operation

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.

# Re: Calculate percent of total with two criteria in one operation

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