SOLVED

Help with Countif criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-1838115%22%20slang%3D%22en-US%22%3EHelp%20with%20Countif%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1838115%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20column%20of%205%20digit%20numbers%20all%20beginning%20with%20the%20same%202%20digits.%20Also%20ignoring%20digits%204%20and%205%2C%20I%20want%20to%20count%20the%20number%20of%20each%20of%20the%20different%203rd%20digits.%20How%20do%20I%20do%20this%20presumably%20using%20the%20%22Countif%22%20function%3F%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1838115%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1838263%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Countif%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1838263%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F852112%22%20target%3D%22_blank%22%3E%40WeeWillie3%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20numbers%20are%20in%20E2%3AE100.%3C%2FP%3E%0A%3CP%3EEnter%20the%20digits%200%2C%201%2C%20...%2C%209%20in%20a%20range%2C%20e.g.%20in%20G2%3AG11.%3C%2FP%3E%0A%3CP%3EEnter%20the%20following%20formula%20in%20H2%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMPRODUCT(--(MID(%24E%242%3A%24E%24100%2C3%2C1)%3DG2%26amp%3B%22%22))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20or%20copy%20down%20to%20H11.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I have a column of 5 digit numbers all beginning with the same 2 digits. Also ignoring digits 4 and 5, I want to count the number of each of the different 3rd digits. How do I do this presumably using the "Countif" function?

Thanks

3 Replies
Highlighted
Best Response confirmed by WeeWillie3 (New Contributor)
Solution

@WeeWillie3 

Let's say the numbers are in E2:E100.

Enter the digits 0, 1, ..., 9 in a range, e.g. in G2:G11.

Enter the following formula in H2:

 

=SUMPRODUCT(--(MID($E$2:$E$100,3,1)=G2&""))

 

Fill or copy down to H11.

Highlighted

@WeeWillie3 

 

MIDMID

 

Hope I was able to help you and would be happy to know if I could help you.

 

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

 

Highlighted

@Hans Vogelaar 

Thanks for your detailed instructions... they worked perfectly!  I just wish I had been smart enough to seek your advice about two hours ago!!!