Sumproduct instead of countif. 1 digit numbers problem.

[Bob Phillips]

SUMPRODUCT will do it

=SUMPRODUCT(--(MID(A1:A10,2,1)="6"))

[fleskj]

SUMPRODUCT will do it

=SUMPRODUCT(--(MID(A1:A10,2,1)="6"))

Ha! I was wondering if there is a simple formula Thanks, xld!
Quick question though, how can I replace this "6" (with the quotes) with a cell reference?
If I've got 6 in the B6 cell for example,
=SUMPRODUCT(--(MID(A1:A10,2,1)=B6)) won't work

[SamT]

I have to admit, this is awesome I have never used VBA before, but your step by step guide is foolproof.
Thank you so much, it works great!
I wish I understood the code itself, but it's a different matter, VBA is on to-do list.
Thanks again, SamT, you saved my dissertation.

In VBA, Place the Keyboard cursor inside a word and press F1 for help on that word.

[fleskj]

Ha! I was wondering if there is a simple formula Thanks, xld!
Quick question though, how can I replace this "6" (with the quotes) with a cell reference?
If I've got 6 in the B6 cell for example,
=SUMPRODUCT(--(MID(A1:A10,2,1)=B6)) won't work

I've figured it out,
it should be
=SUMPRODUCT(--(MID(A1:A10,2,1)+0=B6)) for it to work

EDIT: scratch that, it leaves me with the original problem of 1 digit cells, nevermind

In VBA, Place the Keyboard cursor inside a word and press F1 for help on that word.

Thanks! I'll look into that.

[Bob Phillips]

It doesn't work for the same reason that your original formula doesn't work, you are comparing numeric items with strings. You need to string it

=SUMPRODUCT(--(MID(A1:A10,2,1)=TEXT(B6,"General")))