SOLVED

Help with counting the number of occurencies with filtered data

%3CLINGO-SUB%20id%3D%22lingo-sub-1875758%22%20slang%3D%22en-US%22%3EHelp%20with%20counting%20the%20number%20of%20occurencies%20with%20filtered%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1875758%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20morning%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20count%20the%20number%20of%20times%20the%20numbers%201%2C%202%2C%20%26amp%3B%203%20appear%20in%20the%20RES%20column.%20(Screengrab%20attached)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20data%20is%20filtered%20and%20is%20correctly%20showing%20126%20rows.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20the%20number%20of%20times%20number%20%221%22%20appears%20in%20the%20filtered%20data%20to%20go%20in%20cell%20R8%20The%20Yellow%20cell.%3C%2FP%3E%3CP%3EI%20want%20the%20number%20of%20times%20number%20%222%22%20appears%20in%20the%20filtered%20data%20to%20go%20in%20cell%20S8%20The%20Blue%20cell.%3C%2FP%3E%3CP%3EI%20want%20the%20number%20of%20times%20number%20%223%22%20appears%20in%20the%20filtered%20data%20to%20go%20in%20cell%20T8%20The%20Green%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20can%20anyone%20help%20me%3F%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BKind%20regards%26nbsp%3B%20Dave%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1875758%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1875918%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20counting%20the%20number%20of%20occurencies%20with%20filtered%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1875918%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F388038%22%20target%3D%22_blank%22%3E%40harwood66%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20R8%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMPRODUCT((T11%3AT100%3D1)*(SUBTOTAL(103%2C%20OFFSET(T11%2C%20ROW(T11%3AT100)-MIN(ROW(T11%3AT100))%2C%200))))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EAdjust%20the%20range%20T11%3AT100%20as%20needed.%3C%2FP%3E%0A%3CP%3ESimilar%20for%20S8%20and%20T8%2C%20with%20%3D2%20and%20%3D3%20instead%20of%20%3D1.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1875973%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20counting%20the%20number%20of%20occurencies%20with%20filtered%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1875973%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20ever%20so%20much%2C%20fantastic%20as%20usual.%20Regards%20Dave%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1876085%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20counting%20the%20number%20of%20occurencies%20with%20filtered%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1876085%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20am%20now%20trying%20to%20get%20the%20info%20from%20Col%20BW%20-%20there%20are%20certainly%20more%20than%20two%20occurrences%20in%20that%20column.%20Any%20idea%20what%20is%20wrong%20Hans%3F%20(attached%20file)%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

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

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

@Hans Vogelaar 

 

Thanks ever so much, fantastic as usual. Regards Dave

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

@harwood66 

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

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

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

S0026.png

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

S0027.png

Here is the result:

S0028.png

@Hans Vogelaar Many thanks for your time and trouble Hans