Counting cells - changing criteria

[Xcelfan]

Happy new year everyone

I am trying to figure out how to formulate a countif or similar function that would count the number of cells which would work with changing criteria and accomplish the following scenario.

As you see in the attached image the left column contains the variables and the right column contains the instances each variable occurs individually.

The range is always constant
Though the criteria is ever changing the variables are always numeric and descending (although the example shows the variables ascending)

Any feedback would be much appreciated.

[samba_ravi]

attach sample excel file instead of image

[Xcelfan]

I created and attached a sample workbook per request.

[martindwilson]

what are you counting exactly
are those the results in column e?
where do you want the formula?
i can see there 3 1005 and 5 1005

[Xcelfan]

The attached image and workbook are independent from each other. As I had discarded the workbook associated with the sample image I created a new workbook which I attached to this thread. Therefore if you are checking the workbook you can ignore the original sample image. Also the attached workbook shows the numeric variable in descending order as intended.

Referring to the attached workbook, cells e6 - e11 each show how many instances of each variable is present (for example e6 content is 3 because there are three instances of 1005)
The preferred location of the formula would be in column e where it would populate the cells. The current content of e6-e11 can be replaced or ignored as it is just a representation of what I am trying to accomplish.

[samba_ravi]

E6=IFERROR(INDEX(FREQUENCY(MATCH($C$6:$C$27,$C$6:$C$27,0),ROW(INDIRECT("1:"&COUNTA($C$6:$C$27)))),SMALL(INDEX((FREQUENCY(MATCH($C$6:$C$27,$C$6:$C$27,0),ROW(INDIRECT("1:"&COUNTA($C$6:$C$27))))=0)*10^10+ROW(INDIRECT("1:"&COUNTA($C$6:$C$27)+1)),0),ROWS($F$6:$F6))),"")
Try this and copy towards down

[Glenn Kennedy]

Hi. Try this...

[martindwilson]

a tad more simple no helper columns and a lot shorter!
to get results as shown in e6:e11
e6 =COUNTIF($C$6:$C$200,C6)
e7 =COUNTIF($C$6:$C$200,LARGE($C$6:$C$200,SUM($E$6:E6)+1))
fill down e7

[Vikas_Gautam]

Here is my way to do it..(Non Arrayed)

Please Login or Register  to view this content.

Check the attached file:-

[martindwilson]

is this a longest formula competion?

[Xcelfan]

Thanks everyone for their contribution. I just accessed the forum and saw multiple feedbacks. I'll be going through each response trying to analyze the formulas to see which one would adapt best to my needs. Meanwhile I wanted to express my appreciation again.

[Glenn Kennedy]

I've just been looking at some of these replies. they all have their plus and minus points (setting aside length). One lists the number beside the count, the rest are clever enough not to need to do that. One big difference is how they handle gaps (where a number is missed out). Some solutions return a 0 (but seems to duplicate the last row) whereas others skip the missing number entirely....

Isn't Excel addictive...

[martindwilson]

just change my second formula to
=IFERROR(1/(1/COUNTIF($C$6:$C$200,LARGE($C$6:$C$200,SUM($F$6:F6)+1))),"") will eliminate 0

[martindwilson]

hmmm why did i post this here ah its gone