Forum Discussion

harwood66's avatar
harwood66
Copper Contributor
Nov 11, 2020
Solved

Help with counting the number of occurencies with filtered data

Good morning,   I am trying to count the number of times the numbers 1, 2, & 3 appear in the RES column. (Screengrab attached)   The data is filtered and is correctly showing 126 rows.   I want...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Nov 11, 2020

    harwood66 

    In R8:

    =SUMPRODUCT((T11:T100=1)*(SUBTOTAL(103, OFFSET(T11, ROW(T11:T100)-MIN(ROW(T11:T100)), 0))))

    Adjust the range T11:T100 as needed.

    Similar for S8 and T8, with =2 and =3 instead of =1.

harwood66's avatar
harwood66
Copper Contributor
Nov 11, 2020

HansVogelaar 

 

Thanks ever so much, fantastic as usual. Regards Dave

harwood66's avatar
harwood66
Copper Contributor
Nov 11, 2020

@Hans Vogelaar 

 

I am now trying to get the info from Col BW - there are certainly more than two occurrences in that column. Any idea what is wrong Hans? (attached file) 

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 11, 2020

    harwood66 

    Could you attach a small sample workbook without sensitive information that demonstrates the problem?

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Nov 11, 2020

        harwood66 

        If you set the horizontal alignment of column BW to General, you'll see that all cells except BW11 are left-aligned, indicating that their values are seen as text, not as numbers.

        If you convert the numeric values to real numbers, so that they become right-aligned, the formulas will work as intended.

        Here is the result:

Resources