Counting Rows with SumProduct

  • Thread starter Thread starter Mike M
  • Start date Start date
M

Mike M

I am trying to count the number of row entries that match 2 differen
text criteria. I can make it work with SumProduct as follows, but onl
if I manually add 3 colums of information. I'm looking for a way to d
it without having to add the columns.

Sample of original data
A B
1 Code Location
2 ad123bc AAA
3 rx 234rd BBB
4 wz123eg AAA
5 hv532wh AAA
6 gc123yj BBB
etc

So I am looking to count how many times a code with text
"??123??" appears for location text "AAA" - result = 2.

I have no problem with the location, but I can only seem to make th
Code find correctly by inserting a new column B and C using the LEF
and RIGHT function to 'cut off' the first and last 2 characters of th
Code. (The code is always in the same format). I can then enter 2 exac
texts in the formula.

I also have to add a final column E with all 1's in it, for the formua
to add.

=SUMPRODUCT(($C$2:$C$50="123")*($D$2:$D$50="AAA")*($E$2:$E$50))

So final chart lookas like

A B C D E
1 Code Location Count
2 ad123bc ad123 123 AAA 1
3 rx234rd rx234 234 BBB 1
4 wz123eg wz123 123 AAA 1
5 hv532wh hv532 532 AAA 1
6 gc123yj gc123 123 BBB 1
etc

With the above formula it works fine, but it requires manua
manipulation of the data fields every time I get a data dump file t
analize.

I have tried several ways of entering the first text field a
($A2:$A50="*123*") but this does not appear to work with SumProduct, a
it does for other functions.

So I am looking for a count of every time 123 appears in the middle o
the Code text, and also matches Location text AAA.

??Does countif work matching multiple texts??

Any ideas on how to do this in a better, easier mannor is appreciated.

TIA - Mik
 
...
....
So I am looking to count how many times a code with text
"??123??" appears for location text "AAA" - result = 2.
Click to expand...
....

If you really mean '123' must occupy char positions 3 through 5, use

=SUMPRODUCT(--(MID($C$2:$C$50,3,3)="123"),--($D$2:$D$500="AAA"))

If '123' could be anywhere in the col C cells, another alternative is

=SUMPRODUCT(--(SUBSTITUTE($C$2:$C$500,"123","")<>$C$2:$C$500),
--($D$2:$D$500="AAA"))
 
A very large thanks Jerry, Peo and Harlan.

All 3 solutions do exactly as needed.

Harlan, for your suggestion, you were right to suggest that the "123
may not reside in the 3-5 character location, and that it only need t
be contained within the Code sequence. Therefore your second solutio
is the correct choice in this case.

They all 3 also, of course, make it very easy to adjust the precis
Code and Location to count results for.

Thanks again.
Mike :
 
Back
Top