Counts min and max quantity of equals 'strings' based on a filter column

[RIC63]

Good morning


in Excel 2021


I need to count the minimum and maximum quantity of duplicate strings in J9:J70 according to the value in column H


So in the attached example a formula placed in K3 should return 6, in L3 11, in M3 2 and in N3 9


...I don't know how to modify the formula I put in K3...


Thanks if anyone can help me

[June7]

For a start, figure out how to calc min and max values - How to Find Lowest Value with Criteria in Excel (7 Effective Ways) (exceldemy.com) (https://www.exceldemy.com/excel-find-lowest-value-with-criteria/#:~:text=7%20Effective%20Ways%20to%20Find%20Lowest%20Value%20with,Get%20Low est%20Value%20in%20Excel%20...%20More%20items)

Minimum must be number greater than 1?

You want to use multiple criteria for counting so use COUNTIFS.

In my first simple attempt, values in J cells are read as text and return 0. Converting values to numbers returns maximum and minimum.

Unfortunately, MINIFS() does not seem to be available with Excel 2010 Excel MINIFS function | Exceljet (https://exceljet.net/functions/minifs-function#:~:text=MINIFS%20will%20return%20a%20%23VALUE%20error%20if%20any,% 280%29.%20MINIFS%20ignores%20empty%20cells%20that%20meet%20criteria.), same for COUNTIFS.

Explain the formula some more - why multiply by result of $H$9:$H$70=K1 ?


Wait, you want the count of values that have the least and most rows, not count of the maximum and minimum values? That changes everything for me. Back to square 1.

[Aussiebear]

Not sure if this is the best way to complete your task, but complete the following steps

1. in cell(K9), create an advanced filter, using Copy to Range, List Range of $J$9:$J$70, Criteria range J9, Copy to K8, and select Unique records only. Click OK.

2. This will show a completed range of K9:K18, now in cell L9 enter the formula "=Countif($J$9:$J$70,K9)", and fill down. This gives the count of occurrences.

3. Since you require predetermined values in the following cells K3:N3, namely 6,11,2,9.
in Cell K3 enter the formula "=Small(L9:L18,4)",
in Cell L3 enter the formula "=Large(L9:L18,1)",
in cell M3 enter the formula "=Small(L9:L18,2)", &
in Cell N3 enter the formula "=LargeL9:L18,2)".

[June7]

Aussie, I tried suggestion.

The unique list repeats the 000001 value.

The SMALL() results are not correct.

The LARGE results are correct but I don't think would always be the case. Because, as far as I can tell, these formulas do not take into account grouping defined by H column.

Correction, I do have COUNTIFS available (still not MINIF, MINIFS, MAXIF, MAXIFS). Just not sure it can be helpful.

This does work.

On each row K9:K70, calculate: =COUNTIF($J$9:$J$70,J9)

Then 4 array formulas:

{=MIN(IF($H$9:$H$70=1,$K$9:$K$70))}
{=MAX(IF($H$9:$H$70=1,$K$9:$K$70))}
{=MIN(IF($H$9:$H$70=2,$K$9:$K$70))}
{=MAX(IF($H$9:$H$70=2,$K$9:$K$70))}

Simpler than I expected. You probably have MINIF and MAXIF available.

[RIC63]

Good morning june7 and Aussiebear


thank you for your availability


the last solution of june7 works perfectly and is the simplest to use for me, I just had to adjust the syntax for the separator characters and the name of the functions in Italian


thanks again for your support

[Aussiebear]

Aussie, I tried suggestion.

The unique list repeats the 000001 value.

No it doesn't. Try again.


The SMALL() results are not correct.

Worked here on my sheet.


The LARGE results are correct but I don't think would always be the case.

They met the required predefined results as asked for.



This does work.

On each row K9:K70, calculate: =COUNTIF($J$9:$J$70,J9)

Except the values in K9:K70 are often repeated hence the need to create a Unique listing.


Then 4 array formulas:

{=MIN(IF($H$9:$H$70=1,$K$9:$K$70))}
{=MAX(IF($H$9:$H$70=1,$K$9:$K$70))}
{=MIN(IF($H$9:$H$70=2,$K$9:$K$70))}
{=MAX(IF($H$9:$H$70=2,$K$9:$K$70))}

Simpler than I expected. You probably have MINIF and MAXIF available.

As I tried to indicate, there are probably other methods that will work.

[RIC63]

Sorry June7

It is possible to change the formula {=MIN(IF($H$9:$H$70=1,$K$9:$K$70))} or use another one e.g. MIN.PLUS.IF so that the number 1 is excluded from the counts or the minimum value ?


thanks as much as you can let me know

[June7]

I tried several times. Cannot get your calcs to work. First two rows in the distinct list are 000001. Change the data a bit and even the Large() calcs will not return correct values because not considering the grouping of column H.


As far as I can tell, values do not duplicate between the H groups, however, could allow for that with: =COUNTIFS($H$9:$H$70,H9,$J$9:$J$70,J9)

[RIC63]

ok thanks anyway


I had searched various sites and thought that an instruction to exclude the evaluation of the value 1 could be included in the formula and I thought it was just a matter of using the correct syntax.


thanks again for your support

[June7]

Possibly could with MINIFS and MAXIFS but I don't have those functions available in Excel 2010 to test.

=MINIFS($K$9:$K$70, $H$9:$H$70, 1, $J$9:$J$70, "<>000001")

Review https://professor-excel.com/how-to-use-a-minif-formula-in-excel/

[RIC63]

Hi June7


in fact with the previous solution I had found some inconsistencies


this is a great tip, i have this function in my excel 2021 and it does exactly what i wanted...i missed it in your first post


Thanks again