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!
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: