"contains" question

Pizzio

Active Member
Joined
Apr 28, 2005
Messages
261
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?
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
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

Active Member
Joined
Apr 28, 2005
Messages
261
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

Active Member
Joined
Apr 28, 2005
Messages
261
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
GHIJK
68CountofSitesw/variancein"SLtermenddate"
69JulyJuneMayMarket
70
714-RCHI
72--RDHQ
73--RIND
741-RKSL
75--RNTX
76--ROAR
77--ROWP
782-RSTX
79--RUMW
80
81-7-
Overview
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459

ADVERTISEMENT

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

Active Member
Joined
Apr 28, 2005
Messages
261
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
ABCD
2TermDateReviewJulyJuneMay
3MayRNTXRNTXRNTX
4JuneMayRNTXRNTXRNTX
5MayRNTXRNTXRNTX
6MayRNTXRNTXRNTX
7JuneMayRNTXRNTXRNTX
8MayRNTXRNTXRNTX
9MayRNTXRNTXRNTX
10MayRNTXRNTXRNTX
11JuneMayRNTXRNTXRNTX
12MayRNTXRNTXRNTX
13MayRNTXRNTXRNTX
14MayRNTXRNTXRNTX
15JuneMayRNTXRNTXRNTX
16MayRNTXRNTXRNTX
17MayRNTXRNTXRNTX
18MayRNTXRNTXRNTX
19MayRNTXRNTXRNTX
20MayRNTXRNTXRNTX
21MayRNTXRNTXRNTX
22MayRNTXRNTXRNTX
23JuneMayRNTXRNTXRNTX
24JuneMayRNTXRNTXRNTX
25MayRNTXRNTXRNTX
26MayRNTXRNTXRNTX
27MayRNTXRNTXRNTX
28JuneMayRNTXRNTXRNTX
29MayRNTXRNTXRNTX
30MayRNTXRNTXRNTX
31MayRNTXRNTXRNTX
32JuneMayRNTXRNTXRNTX
33MayRNTXRNTXRNTX
34MayRNTXRNTXRNTX
35MayRNTXRNTXRNTX
36MayRNTXRNTXRNTX
37MayRNTXRNTXRNTX
38MayRNTXRNTXRNTX
39MayRNTXRNTXRNTX
40MayRNTXRNTXRNTX
41MayRNTX0RNTX
Sheet1
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,085
Messages
5,857,273
Members
431,867
Latest member
Dalorian

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top