SOLVED

Contributor

# 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 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

7 Replies
best response confirmed by harwood66 (Contributor)
Solution

# Re: Help with counting the number of occurencies with filtered data

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.

# Re: Help with counting the number of occurencies with filtered data

Thanks ever so much, fantastic as usual. Regards Dave

# Re: Help with counting the number of occurencies with filtered data

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)

# Re: Help with counting the number of occurencies with filtered data

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

# Re: Help with counting the number of occurencies with filtered data

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

# Re: Help with counting the number of occurencies with filtered data

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:

# Re: Help with counting the number of occurencies with filtered data

@Hans Vogelaar Many thanks for your time and trouble Hans