Re: combining countif and mid or right functions

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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
.

Relevant Pages

  • Re: combining countif and mid or right functions
    ... >> I can not get the syntax correct to use countif with right or mid ... >> 3rd digits. ... > Dave Peterson ... Prev by Date: ...
    (microsoft.public.excel)
  • Re: Opening TXT too slow
    ... to make sure the numbers are always treated as 4 digits. ... You may want to review the other suggestion--it shows a way to just look once. ... Dave Peterson ... Prev by Date: ...
    (microsoft.public.excel)
  • Count cells with length not equal to 7
    ... I want to count the number of cells in a column that do not have a value that ... is 7 digits in length. ... I've tried several combinations of CountIf and Len ... Prev by Date: ...
    (microsoft.public.excel.worksheet.functions)
  • combining countif and mid or right functions
    ... I can not get the syntax correct to use countif with right or mid functions. ... 3rd digits. ... Prev by Date: ...
    (microsoft.public.excel)
  • Re: Adding information
    ... If the range is extremely large, =countif() can be slow. ... Dave Peterson wrote: ... > bach wrote: ... Prev by Date: ...
    (microsoft.public.excel.newusers)