Index+match+small+if+countif formula

I

Issie_52

New Member
Joined
Jul 22, 2013
Messages
32
Good day,

I have been using a formula below to calculate SP id's and sale amounts. It worked fine but suddenly it does not pick up the next ID number.

Apologies - I do not know how to paste the example properly.
{=INDEX($H$20:$H$29,MATCH(SMALL(IF(COUNTIF($K$19:K19, $H$20:$H$29)=0, COUNTIF($H$20:$H$29, "<"&$H$20:$H$29), ""), 1), COUNTIF($H$20:$H$29, "<"&$H$20:$H$29), 0))} | SP ID formula

=SUMIF($D$5:$D$15,F7,$D$5:$D$15) | Total Sales formula

Any help will be appreciated. Thanks

[TABLE="width: 0"]
<tbody>[TR]
[TD]Month
[/TD]
[TD]
[/TD]
[TD]SP ID
[/TD]
[TD]Sales
[/TD]
[/TR]
[TR]
[TD]Feb
[/TD]
[TD]
[/TD]
[TD]4
[/TD]
[TD] R 2,280.40
[/TD]
[/TR]
[TR]
[TD]Feb
[/TD]
[TD]
[/TD]
[TD]7
[/TD]
[TD] R 1,144.92
[/TD]
[/TR]
[TR]
[TD]Feb
[/TD]
[TD]
[/TD]
[TD]8
[/TD]
[TD] R 1,144.92
[/TD]
[/TR]
[TR]
[TD]Feb
[/TD]
[TD]
[/TD]
[TD]14
[/TD]
[TD] R 924.14
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]
[/TD]
[TD]SP ID
[/TD]
[TD]Sales
[/TD]
[/TR]
[TR]
[TD]Mar
[/TD]
[TD]
[/TD]
[TD]4
[/TD]
[TD] R 1,144.92
[/TD]
[/TR]
[TR]
[TD]Mar
[/TD]
[TD]
[/TD]
[TD]5
[/TD]
[TD] R 924.14
[/TD]
[/TR]
[TR]
[TD]Mar
[/TD]
[TD]
[/TD]
[TD]14
[/TD]
[TD] R 1,144.92
[/TD]
[/TR]
[TR]
[TD]Mar
[/TD]
[TD]
[/TD]
[TD]52
[/TD]
[TD] R 2,140.14
[/TD]
[/TR]
[TR]
[TD]Mar
[/TD]
[TD]
[/TD]
[TD]64
[/TD]
[TD] R 1,144.92
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]SP ID
[/TD]
[TD]Total Sales
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]4
[/TD]
[TD] R -
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]4
[/TD]
[TD] R -
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]4
[/TD]
[TD] R -
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]4
[/TD]
[TD] R -
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]4
[/TD]
[TD] R -
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]4
[/TD]
[TD] R -
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]4
[/TD]
[TD] R -
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD="colspan: 2"]Must be
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]4
[/TD]
[TD] R3,425.32
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]5
[/TD]
[TD] R 924.14
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]7
[/TD]
[TD] R1,144.92
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]8
[/TD]
[TD] R1,144.92
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]14
[/TD]
[TD] R2,069.06
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]52
[/TD]
[TD] R2,140.14
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]64
[/TD]
[TD] R1,144.92
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
Re: Indes+match+small+if+countif formula

In these cases the advisable would be a pivot table:


<b>Sheet</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:112px;" /><col style="width:95px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td > </td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Month</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">SP ID</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Sales</td><td > </td><td style="background-color:#92d050; ">SP ID</td><td style="background-color:#92d050; ">Sum Sales</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td > </td><td >Feb</td><td style="text-align:right; ">4</td><td style="text-align:right; ">2,280.40</td><td > </td><td style="text-align:center; ">4</td><td style="text-align:right; ">3,425.32</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td > </td><td >Feb</td><td style="text-align:right; ">7</td><td style="text-align:right; ">1,144.92</td><td > </td><td style="text-align:center; ">5</td><td style="text-align:right; ">924.14</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td > </td><td >Feb</td><td style="text-align:right; ">8</td><td style="text-align:right; ">1,144.92</td><td > </td><td style="text-align:center; ">7</td><td style="text-align:right; ">1,144.92</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td > </td><td >Feb</td><td style="text-align:right; ">14</td><td style="text-align:right; ">924.14</td><td > </td><td style="text-align:center; ">8</td><td style="text-align:right; ">1,144.92</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td > </td><td >Mar</td><td style="text-align:right; ">4</td><td style="text-align:right; ">1,144.92</td><td > </td><td style="text-align:center; ">14</td><td style="text-align:right; ">2,069.06</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td > </td><td >Mar</td><td style="text-align:right; ">5</td><td style="text-align:right; ">924.14</td><td > </td><td style="text-align:center; ">52</td><td style="text-align:right; ">2,140.14</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td > </td><td >Mar</td><td style="text-align:right; ">14</td><td style="text-align:right; ">1,144.92</td><td > </td><td style="text-align:center; ">64</td><td style="text-align:right; ">1,144.92</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td > </td><td >Mar</td><td style="text-align:right; ">52</td><td style="text-align:right; ">2,140.14</td><td > </td><td style="background-color:#ffff00; text-align:left; ">Total</td><td style="background-color:#ffff00; text-align:right; ">11,993.42</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td > </td><td >Mar</td><td style="text-align:right; ">64</td><td style="text-align:right; ">1,144.92</td><td > </td><td > </td><td > </td></tr></table> <br /><br />
 
Upvote 0
Re: Indes+match+small+if+countif formula

When you choose the Small formula, you can not sort the data. The following is the closest. Sort the numbers in the auxiliary column to use that order as a parameter in the small formula, but it does not accept references and therefore always puts the same data.

Note: The formula in K20 is an array formula



<b>Sheet</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td > </td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Month</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">SP ID</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Sales</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Aux</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">ID</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">SUM</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td > </td><td >Feb</td><td style="text-align:right; ">4</td><td style="text-align:right; ">2,280.40</td><td style="text-align:right; ">1</td><td style="text-align:right; ">4</td><td style="text-align:right; ">3,425.32</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td > </td><td >Feb</td><td style="text-align:right; ">7</td><td style="text-align:right; ">1,144.92</td><td style="text-align:right; ">3</td><td style="text-align:right; ">7</td><td style="text-align:right; ">1,144.92</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td > </td><td >Feb</td><td style="text-align:right; ">8</td><td style="text-align:right; ">1,144.92</td><td style="text-align:right; ">4</td><td style="text-align:right; ">8</td><td style="text-align:right; ">1,144.92</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td > </td><td >Feb</td><td style="text-align:right; ">14</td><td style="text-align:right; ">924.14</td><td style="text-align:right; ">6</td><td style="text-align:right; ">14</td><td style="text-align:right; ">2,069.06</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td > </td><td >Mar</td><td style="text-align:right; ">4</td><td style="text-align:right; ">1,144.92</td><td > </td><td style="text-align:right; ">5</td><td style="text-align:right; ">924.14</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td > </td><td >Mar</td><td style="text-align:right; ">5</td><td style="text-align:right; ">924.14</td><td style="text-align:right; ">2</td><td style="text-align:right; ">52</td><td style="text-align:right; ">2,140.14</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td > </td><td >Mar</td><td style="text-align:right; ">14</td><td style="text-align:right; ">1,144.92</td><td > </td><td style="text-align:right; ">64</td><td style="text-align:right; ">1,144.92</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td > </td><td >Mar</td><td style="text-align:right; ">52</td><td style="text-align:right; ">2,140.14</td><td style="text-align:right; ">7</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td > </td><td >Mar</td><td style="text-align:right; ">64</td><td style="text-align:right; ">1,144.92</td><td style="text-align:right; ">8</td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >J20</td><td >=IF(COUNTIF($H$20:H20,H20)=1,COUNTIF($H$20:$H$28,"<"&H20)+0+COUNTIF(H21:$H$29,"="&H20),"")</td></tr><tr><td >K20</td><td >=IFERROR(INDEX($H$1:$H$28,SMALL(IF($J$20:$J$28<>"",ROW()),ROW()-19)),"")</td></tr><tr><td >L20</td><td >=SUMIF($H$20:$H$28,K20,$I$20:$I$28)</td></tr></table></td></tr></table> <br /><br />
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Formulas with Inititals with Last Name
Replies
3
Views
98
Fluff
Fluff
A
  • Question Question
Data Selection Based on Closest Value [20% Tolerance]
Replies
1
Views
204
StephenCrump
S
S
  • Solved
Alternate to pivot table
Replies
5
Views
242
Fluff
Fluff
K
  • Question Question
conditional formatting question
Replies
2
Views
585
kerm007
K
Asbestos_Jen
  • Question Question
Compare multiple parameters of samples to reference values (Excel 2007)
Replies
3
Views
359
Asbestos_Jen
Asbestos_Jen

Forum statistics

Threads
1,223,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top