Count the value based on order

[huy_le]

Hi,

I have a column say with 100 values like below:

A B
$100 High
$20 Low
$103 High
.
.
.
$1000 Mid

I want to see if I can count how many times "High" or "Mid" or "Low" appear in the first 25%, the next 25% and so on.

Thanks.

[huy_le]

Nobody has the solution? Maybe to make it simple, instead of $100, $20 and such, let's just make it 1, 2, 3, 4, 5 in order then. I hope I could get a good solution.

[FDibbins]

Forum rules require you to wait at least 24 hours before "bumping" your thread.

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Haseeb Avarakkan]

Take A2:B100 is data.

D2:D4 enter - High, Low & Mid respectively

E1:H1 - 25%, 50%, 75%, 100% respectively

E2, with CTRL+SHIFT+ENTER,

=COUNTIF($B$2:INDEX($B$2:$B$100,MATCH(SUM($A$2:$A$100)*E$1,SUBTOTAL(9,OFFSET($A$2,,,ROW($A$2:$A$100)-ROW($A$2)+1)))),$D2)

Then copy down & across.

Is that you like to achieve?

[huy_le]

Sorry FDibbins, I wasn't trying to pumping it up, I was trying to simplify it so that I could get a solution.

Ideally I want to get the result without having to sort the list in order, but if it's not achievable, sorting is okay too.

Attached is the file.

[Haseeb Avarakkan]

In D5, with CTRL+SHIFT+ENTER

=COUNTIF($B$1:INDEX($B$1:$B$252,MATCH(SUM($A$1:$A$252)*25%,SUBTOTAL(9,OFFSET($A$1,,,ROW($A$1:$A$252)-ROW($A$1)+1)))),D$4)-SUM(D$4:D4)

Change 25% to 50%, 75% & 100% in D6, D7 & D8. Then select D5:D8 then copy across.

[huy_le]

Hi Haseeb,

It's not correct, I notice the last sum contains only one cell, is that the reason? Ok I got this now, forgot to enter array :D

Anyway, what I would like to achieve is actually the 25% of the total count. In this case, we have 252 counts, I want to get the 25% of 252.

Is that achievable?

Thanks so much for your help.

[Haseeb Avarakkan]

Try this Array Formula,

=COUNTIF($B$1:INDEX($B$1:$B$252,MATCH(COUNTA($A$1:$A$252)*25%,SUBTOTAL(3,OFFSET($A$1,,,ROW($A$1:$A$252)-ROW($A$1)+1)))),D$4)-SUM(D$4:D4)

Used COUNTA instead of SUM & used 3 instead of 9 in SUBTOTAL

[huy_le]

That works! Thanks a bunch! What is the difference between 3 and 9, I may ask?

[Haseeb Avarakkan]

There are various options in SUBTOTAL function. ie: SUM a range, COUNT only numbers in a range, count all non blank cells etc... 9 is using to SUM a range, but 3 using for counting non blank cells. If you look on help file can see more about it.

[huy_le]

Ok, great! That was hella complicated formula, don't know how you did it but you did. Thanks so much!