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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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
78
Fluff
Fluff
A
  • Question Question
Data Selection Based on Closest Value [20% Tolerance]
Replies
1
Views
195
StephenCrump
S
S
  • Solved
Alternate to pivot table
Replies
5
Views
225
Fluff
Fluff
K
  • Question Question
conditional formatting question
Replies
2
Views
563
kerm007
K
Asbestos_Jen
  • Question Question
Compare multiple parameters of samples to reference values (Excel 2007)
Replies
3
Views
332
Asbestos_Jen
Asbestos_Jen

Forum statistics

Threads
1,223,061
Messages
6,169,873
Members
452,287
Latest member
winnievmex

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