combining countif and mid or right functions

[Charles Woll]

I can not get the syntax correct to use countif with right or mid functions.
I have a long list of numbers in column A - sample:

404030164
404030173
404030182
404030192
404030204


I need to count the number of 1's in the last digit and 04's in the 2nd and
3rd digits.


--
\\\|///
\\ ~ ~ //
( @ @ )
--oOOo-(_)-oOOo---
Charlie Woll

 

[CLR]

I think I would just use Data > TextToColumns to separate out the digits I
was interested in and then use a regular COUNTIF.......

Vaya con Dios,
Chuck, CABGx3

 

[Dave Peterson]

To get the count of the values that have "04" in the second and third place
(from the left), you could use:
=SUMPRODUCT(--(MID(TEXT(A1:A5,REPT("0",9)),2,2)="04"))

To count the number that end with 1, you could use:
=SUMPRODUCT(--(MOD(A1:A5,10)=1))

=sumproduct() likes to work with numbers. The -- converts Trues and Falses to
+1's and 0's.

 

[RagDyer]

Do you mean all at one time?
Your example doesn't show any 4's in the second position, so a formula would
return a zero count.
Is that what you're looking for?

Or do you mean 3 *separate* counts, one for each condition you mentioned.

Yesterday you received suggestions to count the final "1", which you
acknowledged, so you know the method of approach.

This is if *all 3* conditions must be met concurrently:

=SUMPRODUCT((RIGHT(A1:A1000)="1")*(MID(A1:A1000,2,2)="44"))
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[Charles Woll]

Thank you very much.
charlie woll

To get the count of the values that have "04" in the second and third
place
(from the left), you could use:
=SUMPRODUCT(--(MID(TEXT(A1:A5,REPT("0",9)),2,2)="04"))

To count the number that end with 1, you could use:
=SUMPRODUCT(--(MOD(A1:A5,10)=1))

=sumproduct() likes to work with numbers. The -- converts Trues and
Falses to
+1's and 0's.

 

[Charles Woll]

I have completed my project thanks to all who helped. Now to learn exactly
what is happening in these formulas.
charlie