ISNUMBER Find in text string with Date Range

R

Roxanne9876

New Member
Joined
Apr 6, 2022
Messages
5
Office Version
  1. 2021
Platform
  1. Windows
Hi. The main sheet contains information of the date and the fruits bought in each date. The second sheet (calculations sheet) contains the data I am calculating for.

Issues I faced:

1) I tried for many days to calculate the total number of fruits of each fruit type with date range as the criteria, however my formula is always wrong. I tried this: SUMPRODUCT(--('Main Sheet'!C2:C1000="Orange"),--('Main Sheet'!B2:B1000>="1/1/2019",<="12/31/2019"}) but it is an error.

2) For fruits other than the ones stated, I tried the formula: SUMPRODUCT(--ISNUMBER(FIND({"<>Banana","<>Apple","<>Oranges"},'Main Sheet'!C2:C1000))), however I still get an error.

Hopefully there will be a solution. Appreciate anyone for reading and helping.
 

Attachments

  • Image 1.png
    Image 1.png
    112.4 KB · Views: 19
  • Image 2.png
    Image 2.png
    42.7 KB · Views: 18

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Hi, maybe something along:
Data.xlsx
ABC
1NodateType
219/3/2022Apple
329/10/2022Grape
439/17/2022Pear
5410/1/2022Banana, orange
6510/8/2022Orange
7
8
9
10TypeOrange
11From1/1/2022
12To12/31/2022
13count2
Roxanne9876
Cell Formulas
RangeFormula
B13B13=COUNTIFS(C2:C6,"*"&B10&"*",B2:B6,">="&B11,B2:B6,"<="&B12)
 
Upvote 0
Another option the long way :)

ISNUMBER Find in text string with Date Range_Roxanne9876.xlsx
ABC
1NoDateFruits
213/09/2019Apple,Orange,Pear,Grapes
3210/09/2019Orange,Pear
4317/09/2019Apple
541/10/2020Apple,Orange,banana
652/10/2020Grapes,banana
763/10/2020Apple
8715/10/2021Grapes
9815/10/2021Pear
10915/10/2021Pear,Grapes
111015/10/2021Grapes,Apple
Main Sheet


ISNUMBER Find in text string with Date Range_Roxanne9876.xlsx
ABCDEFGH
1By Fruit Type
2
3OrangeAppleBananaOthers
420192201922019020193
520201202022020220201
620210202112021020215
7
8Others Inc Grape,Pear.
9
Calculations
Cell Formulas
RangeFormula
B4:B6B4=SUMPRODUCT((YEAR('Main Sheet'!$B$2:$B$11)>=Calculations!A4)*(YEAR('Main Sheet'!$B$2:$B$11)<=Calculations!A4)*(ISNUMBER(SEARCH($A$3,'Main Sheet'!$C$2:$C$11))))
D4:D6D4=SUMPRODUCT((YEAR('Main Sheet'!$B$2:$B$11)>=Calculations!A4)*(YEAR('Main Sheet'!$B$2:$B$11)<=Calculations!A4)*ISNUMBER(SEARCH($C$3,'Main Sheet'!$C$2:$C$11)))
F4:F6F4=SUMPRODUCT((YEAR('Main Sheet'!$B$2:$B$11)>=Calculations!A4)*(YEAR('Main Sheet'!$B$2:$B$11)<=Calculations!A4)*ISNUMBER(SEARCH($E$3,'Main Sheet'!$C$2:$C$11)))
H4:H6H4=SUMPRODUCT((YEAR('Main Sheet'!$B$2:$B$11)>=Calculations!A4)*(YEAR('Main Sheet'!$B$2:$B$11)<=Calculations!A4)*ISNUMBER(SEARCH({"Grapes","Pear"},'Main Sheet'!$C$2:$C$11)))
 
Upvote 0
Solution
cyrilbrd said:
Hi, maybe something along:
Data.xlsx
ABC
1NodateType
219/3/2022Apple
329/10/2022Grape
439/17/2022Pear
5410/1/2022Banana, orange
6510/8/2022Orange
7
8
9
10TypeOrange
11From1/1/2022
12To12/31/2022
13count2
Roxanne9876
Cell Formulas
RangeFormula
B13B13=COUNTIFS(C2:C6,"*"&B10&"*",B2:B6,">="&B11,B2:B6,"<="&B12)
Click to expand...
Hi. Thanks for your help :)
cyrilbrd said:
Hi, maybe something along:
Data.xlsx
ABC
1NodateType
219/3/2022Apple
329/10/2022Grape
439/17/2022Pear
5410/1/2022Banana, orange
6510/8/2022Orange
7
8
9
10TypeOrange
11From1/1/2022
12To12/31/2022
13count2
Roxanne9876
Cell Formulas
RangeFormula
B13B13=COUNTIFS(C2:C6,"*"&B10&"*",B2:B6,">="&B11,B2:B6,"<="&B12)
Click to expand...
Hello. Thanks for your help.
 
Upvote 0
RasGhul said:
Another option the long way :)

ISNUMBER Find in text string with Date Range_Roxanne9876.xlsx
ABC
1NoDateFruits
213/09/2019Apple,Orange,Pear,Grapes
3210/09/2019Orange,Pear
4317/09/2019Apple
541/10/2020Apple,Orange,banana
652/10/2020Grapes,banana
763/10/2020Apple
8715/10/2021Grapes
9815/10/2021Pear
10915/10/2021Pear,Grapes
111015/10/2021Grapes,Apple
Main Sheet


ISNUMBER Find in text string with Date Range_Roxanne9876.xlsx
ABCDEFGH
1By Fruit Type
2
3OrangeAppleBananaOthers
420192201922019020193
520201202022020220201
620210202112021020215
7
8Others Inc Grape,Pear.
9
Calculations
Cell Formulas
RangeFormula
B4:B6B4=SUMPRODUCT((YEAR('Main Sheet'!$B$2:$B$11)>=Calculations!A4)*(YEAR('Main Sheet'!$B$2:$B$11)<=Calculations!A4)*(ISNUMBER(SEARCH($A$3,'Main Sheet'!$C$2:$C$11))))
D4:D6D4=SUMPRODUCT((YEAR('Main Sheet'!$B$2:$B$11)>=Calculations!A4)*(YEAR('Main Sheet'!$B$2:$B$11)<=Calculations!A4)*ISNUMBER(SEARCH($C$3,'Main Sheet'!$C$2:$C$11)))
F4:F6F4=SUMPRODUCT((YEAR('Main Sheet'!$B$2:$B$11)>=Calculations!A4)*(YEAR('Main Sheet'!$B$2:$B$11)<=Calculations!A4)*ISNUMBER(SEARCH($E$3,'Main Sheet'!$C$2:$C$11)))
H4:H6H4=SUMPRODUCT((YEAR('Main Sheet'!$B$2:$B$11)>=Calculations!A4)*(YEAR('Main Sheet'!$B$2:$B$11)<=Calculations!A4)*ISNUMBER(SEARCH({"Grapes","Pear"},'Main Sheet'!$C$2:$C$11)))
Click to expand...
Hello, thanks for helping. I actually used your solution and it worked. I prefer the long way.
 
Upvote 0
⁉️ I would be wary of using the formula that you have accepted.
Take this slightly modified example.

Roxanne9876.xlsm
ABC
1NoDateFruits
213/09/2019Apple, Orange, Pear, Grapes
3210/09/2019Orange, Pear, Pineapple
4317/09/2019Apple
541/10/2020Apple, Orange, banana
652/10/2020Grapes, banana
763/10/2020Apple
8715/10/2021Grapes
9815/10/2021Pear
10915/10/2021Pear, Grapes
111015/10/2021Grapes, Apple
Main Sheet


As I understand your requirement, the correct result for "apple" for 2019 would be 2, but the suggested formula returns 3 as shown here. The reason being that it has counted the "apple" within "pineapple" in cell C3.

Roxanne9876.xlsm
ABCD
3OrangeApple
42019220193
52020120202
62021020211
Calculations
Cell Formulas
RangeFormula
B4:B6B4=SUMPRODUCT((YEAR('Main Sheet'!$B$2:$B$11)>=Calculations!A4)*(YEAR('Main Sheet'!$B$2:$B$11)<=Calculations!A4)*(ISNUMBER(SEARCH($A$3,'Main Sheet'!$C$2:$C$11))))
D4:D6D4=SUMPRODUCT((YEAR('Main Sheet'!$B$2:$B$11)>=Calculations!A4)*(YEAR('Main Sheet'!$B$2:$B$11)<=Calculations!A4)*ISNUMBER(SEARCH($C$3,'Main Sheet'!$C$2:$C$11)))


Could you use something like this instead?
I have assumed that the fruits in column C of 'Main Sheet' are separated by comma and space as I think that is what your original image shows.
In future, it would makes things easier if you gave your sample data and expected results with XL2BB

Roxanne9876.xlsm
ABCDE
3OrangeAppleBananaOthers
420192204
520201221
620210105
Calculations (2)
Cell Formulas
RangeFormula
B4:D6B4=LET(dates,'Main Sheet'!$B$2:$B$11,COUNT(FILTER(dates,(YEAR(dates)=$A4)*ISNUMBER(SEARCH(", "&B$3&",",", "&'Main Sheet'!$C$2:$C$11&",")),"")))
E4:E6E4=LET(tj,TEXTJOIN(",",1,FILTER('Main Sheet'!$C$2:$C$11,YEAR('Main Sheet'!$B$2:$B$11)=$A4,"")),LEN(tj)-LEN(SUBSTITUTE(tj,",",""))+1-SUM(B4:D4))
 
Upvote 0
Peter_SSs said:
⁉️ I would be wary of using the formula that you have accepted.
Take this slightly modified example.

Roxanne9876.xlsm
ABC
1NoDateFruits
213/09/2019Apple, Orange, Pear, Grapes
3210/09/2019Orange, Pear, Pineapple
4317/09/2019Apple
541/10/2020Apple, Orange, banana
652/10/2020Grapes, banana
763/10/2020Apple
8715/10/2021Grapes
9815/10/2021Pear
10915/10/2021Pear, Grapes
111015/10/2021Grapes, Apple
Main Sheet


As I understand your requirement, the correct result for "apple" for 2019 would be 2, but the suggested formula returns 3 as shown here. The reason being that it has counted the "apple" within "pineapple" in cell C3.

Roxanne9876.xlsm
ABCD
3OrangeApple
42019220193
52020120202
62021020211
Calculations
Cell Formulas
RangeFormula
B4:B6B4=SUMPRODUCT((YEAR('Main Sheet'!$B$2:$B$11)>=Calculations!A4)*(YEAR('Main Sheet'!$B$2:$B$11)<=Calculations!A4)*(ISNUMBER(SEARCH($A$3,'Main Sheet'!$C$2:$C$11))))
D4:D6D4=SUMPRODUCT((YEAR('Main Sheet'!$B$2:$B$11)>=Calculations!A4)*(YEAR('Main Sheet'!$B$2:$B$11)<=Calculations!A4)*ISNUMBER(SEARCH($C$3,'Main Sheet'!$C$2:$C$11)))


Could you use something like this instead?
I have assumed that the fruits in column C of 'Main Sheet' are separated by comma and space as I think that is what your original image shows.
In future, it would makes things easier if you gave your sample data and expected results with XL2BB

Roxanne9876.xlsm
ABCDE
3OrangeAppleBananaOthers
420192204
520201221
620210105
Calculations (2)
Cell Formulas
RangeFormula
B4:D6B4=LET(dates,'Main Sheet'!$B$2:$B$11,COUNT(FILTER(dates,(YEAR(dates)=$A4)*ISNUMBER(SEARCH(", "&B$3&",",", "&'Main Sheet'!$C$2:$C$11&",")),"")))
E4:E6E4=LET(tj,TEXTJOIN(",",1,FILTER('Main Sheet'!$C$2:$C$11,YEAR('Main Sheet'!$B$2:$B$11)=$A4,"")),LEN(tj)-LEN(SUBSTITUTE(tj,",",""))+1-SUM(B4:D4))
Click to expand...
Hello. Thank you for pointing out the error. I tried your solution and it worked. Just wondering, for RasGhul's solution, instead of using "ISNUMBER(SEARCH", would "ISNUMBER(FIND" / "ISNUMBER(MATCH" be more accurate? Thank you for your help.
 
Upvote 0
Roxanne9876 said:
Hello. Thank you for pointing out the error. I tried your solution and it worked. Just wondering, for RasGhul's solution, instead of using "ISNUMBER(SEARCH", would "ISNUMBER(FIND" / "ISNUMBER(MATCH" be more accurate? Thank you for your help.
Click to expand...
Thanks for the pickup @Peter_SSs

If you replace the Search function with Find as suggested...

ISNUMBER Find in text string with Date Range_Roxanne9876.xlsx
ABC
1NoDateFruits
213/09/2019Apple,Orange,Pear,Grapes
3210/09/2019Orange,Pear,pineapple
4317/09/2019Apple
541/10/2020Apple,Orange,Banana
652/10/2020Grapes,Banana
763/10/2020Apple
8715/10/2021Grapes
9815/10/2021Pear
10915/10/2021Pear,Grapes
111015/10/2021Grapes,Apple
Main Sheet


ISNUMBER Find in text string with Date Range_Roxanne9876.xlsx
ABCDEFGH
1By Fruit Type
2
3OrangeAppleBananaOthers
420192201922019020193
520201202022020220201
620210202112021020215
7
8Others Inc Grape,Pear.
Calculations
Cell Formulas
RangeFormula
D4:D6D4=SUMPRODUCT((YEAR('Main Sheet'!$B$2:$B$11)>=Calculations!A4)*(YEAR('Main Sheet'!$B$2:$B$11)<=Calculations!A4)*ISNUMBER(FIND($C$3,'Main Sheet'!$C$2:$C$11)))
 
Upvote 0
Roxanne9876 said:
Just wondering, for RasGhul's solution, instead of using "ISNUMBER(SEARCH", would "ISNUMBER(FIND" / "ISNUMBER(MATCH" be more accurate?
Click to expand...
I would say a definite "No".

Using FIND with "Apple" would certainly cut out counting "Pineapple" but
- would miss out counting "apple" if it happened to be entered without the capital 'A'
- would still incorrectly count "Custard-Apple", "Rose-Apple" or "Sugar-Apple". It would also incorrectly count "Mangosteen" if using FIND with "Mango" etc.

What it is about my suggestion that ensures looking for correct words, & correct words only, is ensuring the word being looked for is enclosed between commas.

Also, the SUMPRODUCT suggestions above have a redundant year check
(YEAR('Main Sheet'!$B$2:$B$11)>=Calculations!A4)*(YEAR('Main Sheet'!$B$2:$B$11)<=Calculations!A4)
Since you are looking for a particular year with each formula, both the blue section and the red section do exactly the same job. For example, the only way a particular year can be >=2019 and <=2019 is if it is equal to 2019 so there is no need to check for it twice.

Another point about the previous SUMPRODUCT suggestions is that using the sheet name of the sheet that the formulas are actually on is a bad idea as it can lead to incorrect results.
=SUMPRODUCT((YEAR('Main Sheet'!$B$2:$B$11)>=Calculations!A4)*(YEAR('Main Sheet'!$B$2:$B$11)<=Calculations!A4)*ISNUMBER(FIND($C$3,'Main Sheet'!$C$2:$C$11)))
If you want to see what I mean, carefully follow through the steps of the example detailed in this post.

So, if you particularly want to use SUMPRODUCT (not a particularly efficient function) or need the solution to work in earlier versions of Excel then perhaps you could use this.

Roxanne9876.xlsm
BC
1DateFruits
23/09/2019Apple, Orange, Pear, Grapes
310/09/2019Orange, Pear, Custard-Apple
417/09/2019Apple
51/10/2020Apple, Orange, Banana
62/10/2020Grapes, Banana
73/10/2020Apple
815/10/2021Grapes
915/10/2021Pear
1015/10/2021Pear, Grapes
1115/10/2021Grapes, Apple
Main Sheet


Roxanne9876.xlsm
ABCDEFGH
3OrangeCountAppleCountBananaCountOthersCount
420192201922019020194
520201202022020220201
620210202112021020215
Calculations (4)
Cell Formulas
RangeFormula
B4:B6,F4:F6,D4:D6B4=SUMPRODUCT((YEAR('Main Sheet'!$B$2:$B$11)=A4)*ISNUMBER(SEARCH(", "&A$3&",",", "&'Main Sheet'!$C$2:$C$11&",")))
H4:H6H4=SUMPRODUCT((YEAR('Main Sheet'!B$2:B$11)=G4)*(LEN('Main Sheet'!C$2:C$11)-LEN(SUBSTITUTE('Main Sheet'!C$2:C$11,",",""))+1))-SUMIF(B$3:F$3,"Count",B4:F4)
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Lookup a Column Range by Matching Heading
Replies
3
Views
214
jcoeng
J
A
  • Question
SUMIFS exclude value if partial match in list/named range
2
Replies
18
Views
490
Alex Blakenburg
A
E
  • Solved
Index/Match - Return value if partial text from cell contained in separate column
2
Replies
16
Views
2K
Kevin_Li
Kevin_Li
AOB
  • Solved
Formula to seek text in adjacent range and return first matching text
Replies
5
Views
522
AOB
AOB
T
  • Question
Select range that are auto-filtered and have certain text value
Replies
6
Views
509
DanteAmor
DanteAmor

Forum statistics

Threads
1,215,472
Messages
6,125,003
Members
449,203
Latest member
Daymo66

Share this page

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