SUMPRODUCT to count matches using MID for certain placeholders?

Bob McCusker

New Member
Joined
Oct 24, 2009
Messages
42
Yikes.

I have 3 columns (A, B, & C) displaying data in text format; all values look like 00.00, 2 numbers+period+2 numbers.

I am using the following formula in column D, to count instances where the values of A, B, & C from that row down match the current row's particular combination:

=SUMPRODUCT(--(A2:A1000=A2),--(B2:B1000=B2),--(C2:C1000=C2))

I am now trying to make a formula in column E to do the same thing, except this time it should only use the first 2 characters to match. So instead of counting things matching 03.01*01.01*02.04, it would count things matching just 03*01*02.

The best I could come up with after an hour and a half of searching is this:

=SUMPRODUCT(--(MID(A2,1,2):MID(A1000,1,2)=MID(A2,1,2)),--(MID(B2,1,2):MID(B1000,1,2)=MID(B2,1,2)),
--(MID(C2,1,2):MID(C1000,1,2)=MID(C2,1,2)))

which doesn't seem to work at all. I don't know if I am just off a couple parentheses or quotes, or if my approach is flat out wrong.

Any help would be greatly appreciated. Thanks!
 
Last edited:

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

edvwvw

Active Member
Joined
Jan 30, 2007
Messages
278
I am sure that someone will be able to improve on this

=SUMPRODUCT(--(MID(A2:A1000,1,2)="02"))*SUMPRODUCT(--(MID(A2:A1000,1,2)="03")) etc etc


edvwvw
 
Last edited:

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
Try

=SUMPRODUCT(--(LEFT(A2:A1000,2)=LEFT(A2,2)),--(LEFT(B2:B1000,2)=LEFT(B2,2)),--(LEFT(C2:C1000,2)=LEFT(C2,2)))
 

Watch MrExcel Video

Forum statistics

Threads
1,109,132
Messages
5,527,026
Members
409,737
Latest member
shanghity

This Week's Hot Topics

Top