Nov 11 2020 02:07 AM
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
Nov 11 2020 03:10 AM
SolutionIn 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.
Nov 11 2020 03:22 AM
Nov 11 2020 04:13 AM
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)
Nov 11 2020 04:42 AM
Could you attach a small sample workbook without sensitive information that demonstrates the problem?
Nov 11 2020 08:47 AM
@Hans Vogelaar Here it is. AP2, AP3 and AP4 are the working cells
Nov 11 2020 12:16 PM
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:
Nov 12 2020 04:18 AM
@Hans Vogelaar Many thanks for your time and trouble Hans
Nov 11 2020 03:10 AM
SolutionIn 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.