Sumproduct Question

[andy3816]

I have a list of data and use sumproduct to return me various data based on fixed criteria.
I have now been asked to do the same thing but looking for a bunch of text strings that are contained in cell.

Is there a way to use someproduct to return a 1 in an array position based on whether a cell contains one of many text strings

something like

=someproduct(--(A1:A25="2011"),--(B1:B25contains(Text1, or text 2 or text 3)))

I can do this with multiple someproducts however the formula gets too long as there are 20 text variables I need to search for individually it would be great if I could use an OR and get it all into one.


Any help would be greatly appreciated

 

[Dave Patton]

Excel Workbook
	A	B	C	D	E
1		2	2	2
2				Text1	Text2
3
4
5	2010	Text1
6	2011	Text1
7	2011	Text2
8	2011	Text3
9
1a

Excel 2003

Cell Formulas
Range	Formula
B1	=SUMPRODUCT(--(ISNUMBER(MATCH($B$5:$B$100,{"Text1","Text2"},0))),--($A$5:$A$100="2011"))
C1	=SUMPRODUCT(--(ISNUMBER(MATCH($B$5:$B$100,D2:E2,0))),--($A$5:$A$100="2011"))
D1	=SUMPRODUCT(--(($B$5:$B$100=D2)+($B$5:$B$100=D2)),--($A$5:$A$100="2011"))

N.B.
- consistent with your information the example entered the 2010 and 2011 as text

- edit the data ranges and criteria range as required

- the "+" with the formula in D1 is equivalent to "or"

 

[andy3816]

Thanks I did play around with Match but thought it needed an exact match.

Will Match work on part of a string

meaning if the contents of the cell A1 is TEXT1ABC
will Match (Text1,A1,0) return true?

?

 

[T. Valko]

Thanks I did play around with Match but thought it needed an exact match.

Will Match work on part of a string

meaning if the contents of the cell A1 is TEXT1ABC
will Match (Text1,A1,0) return true?

?

If I understand what you want...

Use cells to hold the criteria:

• D2 = 2011

The text strings to match must be entered in a horizontal range of cells:

• E2 = text1
• F2 = text2
• G2 = text3

Then:

=SUMPRODUCT((A2:A10=D2)*(ISNUMBER(SEARCH(E2:G2,B2:B10))))

I notice in your top post you have:

=someproduct(--(A1:A25="2011")

When you quote numbers like that Excel evaluates them as TEXT not numbers.

 

[andy3816]

Thanks for your help, and yes users enter years as text not numbers...strange but true, go figure!!!!

 

[T. Valko]

Thanks for your help, and yes users enter years as text not numbers...strange but true, go figure!!!!

You're welcome. Thanks for the feedback!