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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

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)))
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,384
Messages
5,510,985
Members
408,821
Latest member
OxfordSpires

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top