# SUMPRODUCT to count matches using MID for certain placeholders?

#### Bob McCusker

##### New Member
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:

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### edvwvw

##### Active Member
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
Try

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

#### Bob McCusker

##### New Member
Wow, amazing. You guys are incredible.

THANKS!

Replies
4
Views
84
Replies
1
Views
367
Replies
1
Views
380
Replies
4
Views
177
Replies
6
Views
698