Help with counting the number of occurencies with filtered data

[harwood66]

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 the number of times number "1" appears in the filtered data to go in cell R8 The Yellow cell.

I want the number of times number "2" appears in the filtered data to go in cell S8 The Blue cell.

I want the number of times number "3" appears in the filtered data to go in cell T8 The Green cell.

Please can anyone help me?     Kind regards  Dave

[#2 · Accepted solution]

@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.

[#3]

@Hans Vogelaar 

Thanks ever so much, fantastic as usual. Regards Dave

[#4]

@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) 

[#5]

@harwood66 

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

[#6]

@Hans Vogelaar   Here it is. AP2, AP3 and AP4 are the working cells

[#7]

@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:

[#8]

@Hans Vogelaar Many thanks for your time and trouble Hans