"contains" question

Pizzio

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

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Brian from Maui

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

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
250
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,458
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,655
Messages
5,597,384
Members
414,141
Latest member
Joey_T92

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