Discussion Re: Calculate percent of total with two criteria in one operation in Excel
https://techcommunity.microsoft.com/t5/excel/calculate-percent-of-total-with-two-criteria-in-one-operation/m-p/3244012#M136805
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.<BR /><BR />Thank you.Thu, 03 Mar 2022 02:40:04 GMTToots9122022-03-03T02:40:04ZCalculate percent of total with two criteria in one operation
https://techcommunity.microsoft.com/t5/excel/calculate-percent-of-total-with-two-criteria-in-one-operation/m-p/2420283#M102405
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.<BR /><BR />There must be a better way to do this, right? Maybe with vlookup? I'm an intermediate Excel user at best<BR />Sun, 06 Jun 2021 19:50:52 GMThttps://techcommunity.microsoft.com/t5/excel/calculate-percent-of-total-with-two-criteria-in-one-operation/m-p/2420283#M102405MrGTH2021-06-06T19:50:52ZRe: Calculate percent of total with two criteria in one operation
https://techcommunity.microsoft.com/t5/excel/calculate-percent-of-total-with-two-criteria-in-one-operation/m-p/2420332#M102406
<P><LI-USER uid="1072303"></LI-USER> </P>
<P>A simple option:</P>
<P> </P>
<P>=SUM(B28,B30)/SUM(B28:B32)</P>
<P> </P>
<P>Format the cell with the formula as a percentage.</P>
<P> </P>
<P>More sophisticated:</P>
<P> </P>
<P>=(SUMIF(A28:A32,"Agent 1",B28:B32)+SUMIF(A28:A32,"Agent 3",B28:B32))/SUM(B28:B32)</P>
<P> </P>
<P>or</P>
<P> </P>
<P>=(VLOOKUP("Agent 1",A28:B32,2,FALSE)+VLOOKUP("Agent 3",A28:B32,2,FALSE))/SUM(B28:B32)</P>Sun, 06 Jun 2021 20:15:10 GMThttps://techcommunity.microsoft.com/t5/excel/calculate-percent-of-total-with-two-criteria-in-one-operation/m-p/2420332#M102406Hans Vogelaar2021-06-06T20:15:10ZRe: Calculate percent of total with two criteria in one operation
https://techcommunity.microsoft.com/t5/excel/calculate-percent-of-total-with-two-criteria-in-one-operation/m-p/2420335#M102407
Great, thank you so much!<BR />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.<BR /><BR />However I do understand the SUMIF operation, but it's giving me 340.16% for some reasonSun, 06 Jun 2021 20:23:18 GMThttps://techcommunity.microsoft.com/t5/excel/calculate-percent-of-total-with-two-criteria-in-one-operation/m-p/2420335#M102407MrGTH2021-06-06T20:23:18ZRe: Calculate percent of total with two criteria in one operation
https://techcommunity.microsoft.com/t5/excel/calculate-percent-of-total-with-two-criteria-in-one-operation/m-p/2420364#M102408
<P><LI-USER uid="1072303"></LI-USER> </P>
<P>All three formulas should produce the same result. With the data from your photo, I get this:</P>
<P> </P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="S0484.png" style="width: 539px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/286670iDB5BF324AAF31FDF/image-size/large?v=v2&px=999" role="button" title="S0484.png" alt="S0484.png" /></span></P>
<P> </P>
<P>The formula VLOOKUP("Agent 1",A28:B32,2,FALSE) does the following:</P>
<P>It looks for the value <STRONG>"Agent 1"</STRONG> in the first column of the range <STRONG>A28:B32</STRONG>, i.e. in A28:A32.</P>
<P>If found, it returns the corresponding value from the <STRONG>2</STRONG>nd column of A28:B32, i.e. from B28:B32.</P>
<P>The last argument <STRONG>FALSE</STRONG> tells Excel to look for an exact match.</P>Sun, 06 Jun 2021 20:54:42 GMThttps://techcommunity.microsoft.com/t5/excel/calculate-percent-of-total-with-two-criteria-in-one-operation/m-p/2420364#M102408Hans Vogelaar2021-06-06T20:54:42ZRe: Calculate percent of total with two criteria in one operation
https://techcommunity.microsoft.com/t5/excel/calculate-percent-of-total-with-two-criteria-in-one-operation/m-p/2420370#M102409
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 makingSun, 06 Jun 2021 21:00:28 GMThttps://techcommunity.microsoft.com/t5/excel/calculate-percent-of-total-with-two-criteria-in-one-operation/m-p/2420370#M102409MrGTH2021-06-06T21:00:28ZRe: Calculate percent of total with two criteria in one operation
https://techcommunity.microsoft.com/t5/excel/calculate-percent-of-total-with-two-criteria-in-one-operation/m-p/2420372#M102410
<P><LI-USER uid="1072303"></LI-USER> </P>
<P>You omitted the comma between A28:A32 and "Agent 1", and also between A28:A32 and "Agent 3".</P>Sun, 06 Jun 2021 21:08:21 GMThttps://techcommunity.microsoft.com/t5/excel/calculate-percent-of-total-with-two-criteria-in-one-operation/m-p/2420372#M102410Hans Vogelaar2021-06-06T21:08:21ZRe: Calculate percent of total with two criteria in one operation
https://techcommunity.microsoft.com/t5/excel/calculate-percent-of-total-with-two-criteria-in-one-operation/m-p/2420380#M102411
Thank you so much. That was the mistake indeed. I tried it now and it gave me the correct result!Sun, 06 Jun 2021 21:13:14 GMThttps://techcommunity.microsoft.com/t5/excel/calculate-percent-of-total-with-two-criteria-in-one-operation/m-p/2420380#M102411MrGTH2021-06-06T21:13:14ZRe: Calculate percent of total with two criteria in one operation
https://techcommunity.microsoft.com/t5/excel/calculate-percent-of-total-with-two-criteria-in-one-operation/m-p/3239416#M136639
Hi,<BR />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.<BR /><BR />My sample (My table is a pivot table)<BR /><BR />FILTER: ALL Customers (option to select individual customers to see change in %)<BR />MODEL BUILD SIZE QTY QTY %<BR />Model 1 Build A SM 5<BR />Model 1 Build B MD 3<BR />Model 2 Build A SM 2<BR />Model 2 Build B MD 3<BR />Model 2 Build C LG 2<BR />Model 3 Build A SM 10<BR />Model 3 Build B MD 4<BR /><BR />Results looking to achieve: QTY % for MODEL + BUILD + SIZE<BR /><BR />Thank you.Wed, 02 Mar 2022 02:57:19 GMThttps://techcommunity.microsoft.com/t5/excel/calculate-percent-of-total-with-two-criteria-in-one-operation/m-p/3239416#M136639Toots9122022-03-02T02:57:19ZRe: Calculate percent of total with two criteria in one operation
https://techcommunity.microsoft.com/t5/excel/calculate-percent-of-total-with-two-criteria-in-one-operation/m-p/3240877#M136689
<P><LI-USER uid="1308509"></LI-USER> </P>
<P>Could you attach a sample workbook, or make it available through OneDrive, Google Drive, Dropbox or similar?</P>Wed, 02 Mar 2022 11:55:42 GMThttps://techcommunity.microsoft.com/t5/excel/calculate-percent-of-total-with-two-criteria-in-one-operation/m-p/3240877#M136689Hans Vogelaar2022-03-02T11:55:42ZRe: Calculate percent of total with two criteria in one operation
https://techcommunity.microsoft.com/t5/excel/calculate-percent-of-total-with-two-criteria-in-one-operation/m-p/3240936#M136694
<P><LI-USER uid="127945"></LI-USER> 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.</P><P> </P><P>Hope this helps, and thank you for your reply. Much appreciate the help.</P><P><BR />Thank you.</P><P>Toots.</P>Wed, 02 Mar 2022 12:38:50 GMThttps://techcommunity.microsoft.com/t5/excel/calculate-percent-of-total-with-two-criteria-in-one-operation/m-p/3240936#M136694Toots9122022-03-02T12:38:50ZRe: Calculate percent of total with two criteria in one operation
https://techcommunity.microsoft.com/t5/excel/calculate-percent-of-total-with-two-criteria-in-one-operation/m-p/3241105#M136703
<P><LI-USER uid="1308509"></LI-USER> </P>
<P>In E4:</P>
<P>=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)),"")</P>
<P>If you don't have Microsoft 365 or Office 2021, you may have to confirm the formula with Ctrl+Shift+Enter.</P>
<P>Fill down.</P>Wed, 02 Mar 2022 13:31:38 GMThttps://techcommunity.microsoft.com/t5/excel/calculate-percent-of-total-with-two-criteria-in-one-operation/m-p/3241105#M136703Hans Vogelaar2022-03-02T13:31:38ZRe: Calculate percent of total with two criteria in one operation
https://techcommunity.microsoft.com/t5/excel/calculate-percent-of-total-with-two-criteria-in-one-operation/m-p/3241165#M136708
Thank you. It works!<BR />I'll study the formula to get a better understanding of how it calculates.<BR />Wed, 02 Mar 2022 13:47:30 GMThttps://techcommunity.microsoft.com/t5/excel/calculate-percent-of-total-with-two-criteria-in-one-operation/m-p/3241165#M136708Toots9122022-03-02T13:47:30ZRe: Calculate percent of total with two criteria in one operation
https://techcommunity.microsoft.com/t5/excel/calculate-percent-of-total-with-two-criteria-in-one-operation/m-p/3244012#M136805
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.<BR /><BR />Thank you.Thu, 03 Mar 2022 02:40:04 GMThttps://techcommunity.microsoft.com/t5/excel/calculate-percent-of-total-with-two-criteria-in-one-operation/m-p/3244012#M136805Toots9122022-03-03T02:40:04ZRe: Calculate percent of total with two criteria in one operation
https://techcommunity.microsoft.com/t5/excel/calculate-percent-of-total-with-two-criteria-in-one-operation/m-p/3244719#M136829
<P><LI-USER uid="1308509"></LI-USER> </P>
<P>See the documentation for the <A href="https://support.microsoft.com/en-us/office/subtotal-function-7b027003-f060-4ade-9040-e478765b9939" target="_blank" rel="noopener">SUBTOTAL function</A>.</P>
<TABLE class="banded flipColors">
<THEAD>
<TR>
<TH width="209.167px" height="55px">
<P><STRONG class="ocpLegacyBold">Function_num</STRONG> <BR /><STRONG class="ocpLegacyBold">(includes hidden rows)</STRONG></P>
</TH>
<TH width="201.817px" height="55px">
<P><STRONG class="ocpLegacyBold">Function_num</STRONG> <BR /><STRONG class="ocpLegacyBold">(ignores hidden rows)</STRONG></P>
</TH>
<TH width="81.95px" height="55px">
<P><STRONG class="ocpLegacyBold">Function</STRONG></P>
</TH>
</TR>
</THEAD>
<TBODY>
<TR>
<TD width="209.167px" height="30px">
<P>1</P>
</TD>
<TD width="201.817px" height="30px">
<P>101</P>
</TD>
<TD width="81.95px" height="30px">
<P>AVERAGE</P>
</TD>
</TR>
<TR>
<TD width="209.167px" height="30px">
<P>2</P>
</TD>
<TD width="201.817px" height="30px">
<P>102</P>
</TD>
<TD width="81.95px" height="30px">
<P>COUNT</P>
</TD>
</TR>
<TR>
<TD width="209.167px" height="30px">
<P>3</P>
</TD>
<TD width="201.817px" height="30px">
<P>103</P>
</TD>
<TD width="81.95px" height="30px">
<P>COUNTA</P>
</TD>
</TR>
<TR>
<TD width="209.167px" height="30px">
<P>4</P>
</TD>
<TD width="201.817px" height="30px">
<P>104</P>
</TD>
<TD width="81.95px" height="30px">
<P>MAX</P>
</TD>
</TR>
<TR>
<TD width="209.167px" height="30px">
<P>5</P>
</TD>
<TD width="201.817px" height="30px">
<P>105</P>
</TD>
<TD width="81.95px" height="30px">
<P>MIN</P>
</TD>
</TR>
<TR>
<TD width="209.167px" height="30px">
<P>6</P>
</TD>
<TD width="201.817px" height="30px">
<P>106</P>
</TD>
<TD width="81.95px" height="30px">
<P>PRODUCT</P>
</TD>
</TR>
<TR>
<TD width="209.167px" height="30px">
<P>7</P>
</TD>
<TD width="201.817px" height="30px">
<P>107</P>
</TD>
<TD width="81.95px" height="30px">
<P>STDEV</P>
</TD>
</TR>
<TR>
<TD width="209.167px" height="30px">
<P>8</P>
</TD>
<TD width="201.817px" height="30px">
<P>108</P>
</TD>
<TD width="81.95px" height="30px">
<P>STDEVP</P>
</TD>
</TR>
<TR>
<TD width="209.167px" height="30px">
<P>9</P>
</TD>
<TD width="201.817px" height="30px">
<P><STRONG>109</STRONG></P>
</TD>
<TD width="81.95px" height="30px">
<P><STRONG>SUM</STRONG></P>
</TD>
</TR>
<TR>
<TD width="209.167px" height="30px">
<P>10</P>
</TD>
<TD width="201.817px" height="30px">
<P>110</P>
</TD>
<TD width="81.95px" height="30px">
<P>VAR</P>
</TD>
</TR>
<TR>
<TD width="209.167px" height="30px">
<P>11</P>
</TD>
<TD width="201.817px" height="30px">
<P>111</P>
</TD>
<TD width="81.95px" height="30px">
<P>VARP</P>
</TD>
</TR>
</TBODY>
</TABLE>Thu, 03 Mar 2022 10:24:11 GMThttps://techcommunity.microsoft.com/t5/excel/calculate-percent-of-total-with-two-criteria-in-one-operation/m-p/3244719#M136829Hans Vogelaar2022-03-03T10:24:11Z