combining countif and mid or right functions

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

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



"Charles Woll" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>

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 wrote:
>
> 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
>

--

Dave Peterson

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>

Thank you very much.
charlie woll

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
>>
>> 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
>>
>
> --
>
> Dave Peterson

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

"RagDyer" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>>
>