"contains" question

[Pizzio]

Is there a formula that i can use that brings in a count of items that contains a text? Currentlyi am trying to use sumproduct to bring in the data i need such as June, July, August, etc. but the column i need to pull the data from has several instances where the results are 'June July' so the count is missed in this case. Is there a way to make the sumproduct return a result if the field contains the work June or July or whatever?

 

[Brian from Maui]

Is there a formula that i can use that brings in a count of items that contains a text? Currentlyi am trying to use sumproduct to bring in the data i need such as June, July, August, etc. but the column i need to pull the data from has several instances where the results are 'June July' so the count is missed in this case. Is there a way to make the sumproduct return a result if the field contains the work June or July or whatever?

A little confusing, but,

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A10,{"June","July"},0))),--(B1:B10="Criteria))

 

[Pizzio]

I tried the formula but it isn't working for me, Does the {"June","july"} portion of the formula only draw in one of the months?

 

[Pizzio]

As you can see in column H it returned a value of 4 and a total of 7 but there are actually 8 and all are in market RNTX. What am i doing wrong?

RCCW SL Review 07-31-07 v1.xls
	G	H	I	J	K
68	CountofSitesw/variancein"SLtermenddate"
69	July	June	May		Market
70
71		4	-		RCHI
72		-	-		RDHQ
73		-	-		RIND
74		1	-		RKSL
75		-	-		RNTX
76		-	-		ROAR
77		-	-		ROWP
78		2	-		RSTX
79		-	-		RUMW
80
81	-	7	-
Overview

 

[Brian from Maui]

Can you post a sample of what is housed in U5:U15010 and AB5:AB15010? If you want a count of June OR July, include July in the formula as in {"June","July"}.

 

[Pizzio]

As you can see i'm trying to match the market (column B here) up first then in column U (column A here) with anything that contains June or July, or May but individually.

Book1
	A	B	C	D
2	TermDateReview	July	June	May
3	May	RNTX	RNTX	RNTX
4	JuneMay	RNTX	RNTX	RNTX
5	May	RNTX	RNTX	RNTX
6	May	RNTX	RNTX	RNTX
7	JuneMay	RNTX	RNTX	RNTX
8	May	RNTX	RNTX	RNTX
9	May	RNTX	RNTX	RNTX
10	May	RNTX	RNTX	RNTX
11	JuneMay	RNTX	RNTX	RNTX
12	May	RNTX	RNTX	RNTX
13	May	RNTX	RNTX	RNTX
14	May	RNTX	RNTX	RNTX
15	JuneMay	RNTX	RNTX	RNTX
16	May	RNTX	RNTX	RNTX
17	May	RNTX	RNTX	RNTX
18	May	RNTX	RNTX	RNTX
19	May	RNTX	RNTX	RNTX
20	May	RNTX	RNTX	RNTX
21	May	RNTX	RNTX	RNTX
22	May	RNTX	RNTX	RNTX
23	JuneMay	RNTX	RNTX	RNTX
24	JuneMay	RNTX	RNTX	RNTX
25	May	RNTX	RNTX	RNTX
26	May	RNTX	RNTX	RNTX
27	May	RNTX	RNTX	RNTX
28	JuneMay	RNTX	RNTX	RNTX
29	May	RNTX	RNTX	RNTX
30	May	RNTX	RNTX	RNTX
31	May	RNTX	RNTX	RNTX
32	JuneMay	RNTX	RNTX	RNTX
33	May	RNTX	RNTX	RNTX
34	May	RNTX	RNTX	RNTX
35	May	RNTX	RNTX	RNTX
36	May	RNTX	RNTX	RNTX
37	May	RNTX	RNTX	RNTX
38	May	RNTX	RNTX	RNTX
39	May	RNTX	RNTX	RNTX
40	May	RNTX	RNTX	RNTX
41	May	RNTX	0	RNTX
Sheet1

 

[Brian from Maui]

As you can see i'm trying to match the market (column B here) up first then in column U (column A here) with anything that contains June or July, or May but individually.

I'm not understanding this part. In your latest example, are you counting May in column A then in column D? What is your expected answer from your example?