"contains" question

Pizzio

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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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))
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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"}.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,211,803
Messages
6,104,067
Members
447,892
Latest member
madara uchiha

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
Back
Top