ISNUMBER Find in text string with Date Range

[Roxanne9876]

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.

 

[Roxanne9876]

Tha

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
	B	C
1	Date	Fruits
2	3/09/2019	Apple, Orange, Pear, Grapes
3	10/09/2019	Orange, Pear, Custard-Apple
4	17/09/2019	Apple
5	1/10/2020	Apple, Orange, Banana
6	2/10/2020	Grapes, Banana
7	3/10/2020	Apple
8	15/10/2021	Grapes
9	15/10/2021	Pear
10	15/10/2021	Pear, Grapes
11	15/10/2021	Grapes, Apple
Main Sheet

Roxanne9876.xlsm
	A	B	C	D	E	F	G	H
3	Orange	Count	Apple	Count	Banana	Count	Others	Count
4	2019	2	2019	2	2019	0	2019	4
5	2020	1	2020	2	2020	2	2020	1
6	2021	0	2021	1	2021	0	2021	5
Calculations (4)

Cell Formulas
Range	Formula
B4:B6,F4:F6,D4:D6	B4	=SUMPRODUCT((YEAR('Main Sheet'!$B$2:$B$11)=A4)*ISNUMBER(SEARCH(", "&A$3&",",", "&'Main Sheet'!$C$2:$C$11&",")))
H4:H6	H4	=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)

Thank you for your detailed explanation. Appreciate that you took time out to help

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.