Help with COUNTIF formula

[JumboCactuar]

Hi,
i have a row of data containing 8 digit numbers and i want to count the number of 10,20 or 30 at the 5th and 6th digit
is there an easy way to do this?

Example: data in column A

10053001
24224002
14432001
54419030
44301002
10223001
55677501

not sure what to put in for the criteria but formula from above data should output a value of 4

=COUNTIFS($A:$A,??)

i could do it by creating another formula =MID(A2,5,2) but would prefer not to as theres lots of data

any help appreciated

 

[Tetra201]

This would work:

=SUMPRODUCT((A2:A1000=B5)*(MID(F2:F1000,5,2)={"10","20","30"}))

 

[JumboCactuar]

This would work:

=SUMPRODUCT((A2:A1000=B5)*(MID(F2:F1000,5,2)={"10","20","30"}))

perfect thankyou

 

[JumboCactuar]

last bit of help needed

this counts the number of cells, but how would i SUM the values of H2:H1000 from the given criteria

=SUMPRODUCT((A2:A1000=B5)*(MID(F2:F1000,5,2)={"10","20","30"}))

i would usually use SUMIFS not sure how to combine it here

thanks

 

[Joe4]

Try:

[COLOR=#333333]=SUMPRODUCT((A2:A1000=B5)*(MID(F2:F1000,5,2)={"10","20","30"})[/COLOR][COLOR=#ff0000]*(H2:H1000)[/COLOR])

 

[Aladin Akyurek]

1.

=SUMPRODUCT(--(A2:A1000=B5),--ISNUMBER(MATCH(MID(F2:F1000,5,2)+0,{10,20,30},0)))

2.

=SUMPRODUCT(H2:H1000,--(A2:A1000=B5),--ISNUMBER(MATCH(MID(F2:F1000,5,2)+0,{10,20,30},0)))_{^{<strike>
</strike>}}

 

[JumboCactuar]

Try:

[COLOR=#333333]=SUMPRODUCT((A2:A1000=B5)*(MID(F2:F1000,5,2)={"10","20","30"})[/COLOR][COLOR=#ff0000]*(H2:H1000)[/COLOR])

working