=IF SUMPRODUCT Formula

[uk_2022]

I have got a =if sumproduct formula below

=IF(SUMPRODUCT((I9<$K$9:$K$17)*(K9>$I$9:$I$17)*(N9=$N$9:$N$17))>1,"Check","Available")

however I need the (N9=$N$9:$N$17) section to find values within the text.

Values in column N are entered from a drop down list (a VBA allow multiple selections).

I have attached a image of my worksheet;

Example: Check in dates overlap and both rows contain "Birdie Compact Hoist" in column N, however row 10 also contains "Aquatec......." in the same cell.

Currently, column P is returning "available" as per the formula above as it is not picking up that "Birdie Compact Hoist" has appeared in both cells.

Can anyone suggest a solution how the formula can pick out matching values from the text in column N?

TIA

 

[SanjayGMusafir]

I only need it to return "Check" if the date overlap others AND the equipment in column N match. I.E if both rows had "Birdie Compact Hoist" within the values. (See row 10 - it also has "Geeco....." but I still need the formula to recognise that "Birdie....." appears with the text.

This is happening because when N10 doesn't exactly matches N9

I'm trying to think of some workaround.

 

[uk_2022]

another user suggested

=IF(COUNTIFS($K$9:$K$17,">"&I9,$I$9:$I$17,"<"&K9,$N$9:$N$17,"*"&N9&"*")>1,"Check","Available")

which did recognize the text match but it returns one row as "Check" and the other as "Available". I need it so return "check" for both so that user can then amend equipment required to prevent the "check" from occurring.

 

[uk_2022]

Equipment Log Template.xlsm
	I	J	K		M	N	O	P	Q	R
8	Check in Date	Nights	Check Out		Archive	CP Equipment Required	3rd Party Equipment Requested	Availability	PO Required	PO Number
9	Fri 26 Aug 2022	3	Mon 29 Aug 2022		FALSE	Birdie Compact Hoist		Check
10	Fri 26 Aug 2022	7	Fri 02 Sep 2022		FALSE	Aquatec Ocean (XL) Shower/over toilet chair with small wheels Birdie Compact Hoist		Available
11								Available
12								Available
13								Available
14								Available
15								Available
16								Available
17								Available
Equipment

Cell Formulas
Range	Formula
K9:K17	K9	=IF(J9="","",I9+J9)
M9:M17	M9	=IF(J9="","",IF(K9<TODAY(),TRUE,FALSE))
P9:P17	P9	=IF(COUNTIFS($K$9:$K$17,">"&I9,$I$9:$I$17,"<"&K9,$N$9:$N$17,"*"&N9&"*")>1,"Check","Available")

Cells with Data Validation
Cell	Allow	Criteria
Q9:Q17	List	To Raise, Raised
N9:N17	List	=Lists!$A$2:$A$12
O9:O17	List	=Lists!$B$2:$B$23

 

[SanjayGMusafir]

another user suggested

=IF(COUNTIFS($K$9:$K$17,">"&I9,$I$9:$I$17,"<"&K9,$N$9:$N$17,"*"&N9&"*")>1,"Check","Available")

which did recognize the text match but it returns one row as "Check" and the other as "Available". I need it so return "check" for both so that user can then amend equipment required to prevent the "check" from occurring.

Either ways it will match the exact values of a cell and return accordingly.

Instead of having 1 column N for Equipment required, Can we have 2 or more Columns to avoid this error.

 

[uk_2022]

Either ways it will match the exact values of a cell and return accordingly.

Instead of having 1 column N for Equipment required, Can we have 2 or more Columns to avoid this error.

do you mean have a column for each piece of equipment?

how would that work with the formula?

 

[uk_2022]

						Blue Wheeled Commode	Linton Plus Raised Toilet Seat	Geeco Small White Shower Chair	Aquatec Ocean (XL) Shower/over toilet chair with small wheels	Aquatec Ocean (XL) Shower chair only with large wheels	Bed Rail Inserts for Bakare Profiling Beds	Birdie Compact Hoist	Sunlift Micro Hoist	Hoist (TBC)	Sunlift Micro and Birdie Compact (Both)
	Existing booking for dates
	For delivery this week
	For collection this week		SORT COLUMN
Lodge Number	Check in Date	Nights	Check Out		Archive											3rd Party Equipment Requested	Availability
	Fri 26 Aug 2022	3	Mon 29 Aug 2022		FALSE							Yes
	Fri 26 Aug 2022	7	Fri 02 Sep 2022		FALSE				Yes			Yes
	Mon 29 Aug 2022	4	Fri 02 Sep 2022		FALSE

 

[SanjayGMusafir]

do you mean have a column for each piece of equipment?

how would that work with the formula?

After much thought, I found it was better to make some helper columns - see Column Z onwards. That would make things and formula simple and short.

Check if this works for you

If I get some better solution I shall revert back with that.

Meanwhile if some other experts looks at it and they might suggest a better solution.

Book1
	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
1
2							Blue Wheeled Commode	Linton Plus Raised Toilet Seat	Geeco Small White Shower Chair	Aquatec Ocean (XL) Shower/over toilet chair with small wheels	Aquatec Ocean (XL) Shower chair only with large wheels	Bed Rail Inserts for Bakare Profiling Beds	Birdie Compact Hoist	Sunlift Micro Hoist	Hoist (TBC)	Sunlift Micro and Birdie Compact (Both)
3		Existing booking for dates
4		For delivery this week
5		For collection this week		SORT COLUMN
6
7
8	Lodge Number	Check in Date	Nights	Check Out		Archive											3rd Party Equipment Requested	Availability	CheckDt	Check1	Check2	Check3	Check4	Check5	Check6	Check7	Check8	Check9	Check10
9		44799	3	44802		FALSE							Yes					Check	2	0	0	0	0	0	0	2	0	0	0
10		44799	7	44806		FALSE				Yes			Yes					Check	3	0	0	0	1	0	0	2	0	0	0
11		44802	4	44806		FALSE												Available	2	0	0	0	0	0	0	0	0	0	0
12																			0	0	0	0	0	0	0	0	0	0	0
13																			0	0	0	0	0	0	0	0	0	0	0
14																			0	0	0	0	0	0	0	0	0	0	0
15																			0	0	0	0	0	0	0	0	0	0	0
16																			0	0	0	0	0	0	0	0	0	0	0
17																			0	0	0	0	0	0	0	0	0	0	0
Sheet2

Cell Formulas
Range	Formula
Y9:Y17	Y9	=IFS(AND(I9="",COUNTA(N9:W9)=0),"",AND(Z9>1,COUNTIFS(AA9:AJ9,">"&1)>0),"Check",TRUE,"Available")
Z9:Z17	Z9	=SUMPRODUCT((I9<$K$9:$K$17)*(K9>$I$9:$I$17))
AA9:AJ17	AA9	=SUMPRODUCT((N$9:N$17<>"")*(N$9:N$17=N9))
K9:K11	K9	=IF(J9="","",I9+J9)
M9:M11	M9	=IF(J9="","",IF(K9<TODAY(),TRUE,FALSE))

 

[uk_2022]

Thanks for that however issue with the dates section. It is returning "check" for dates that don't overlap but do check in/out on same dates.

i.e row 9 - check in 26.08.22 and check out 29.08.22
row 10 - check in 29.08.2022 and check out 02.09.2022

 

[SanjayGMusafir]

Thanks for that however issue with the dates section. It is returning "check" for dates that don't overlap but do check in/out on same dates.

i.e row 9 - check in 26.08.22 and check out 29.08.22
row 10 - check in 29.08.2022 and check out 02.09.2022

Minor change in formula in Column 9 to check overlaps.

Check if this works for you.

Book1
	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
1
2							Blue Wheeled Commode	Linton Plus Raised Toilet Seat	Geeco Small White Shower Chair	Aquatec Ocean (XL) Shower/over toilet chair with small wheels	Aquatec Ocean (XL) Shower chair only with large wheels	Bed Rail Inserts for Bakare Profiling Beds	Birdie Compact Hoist	Sunlift Micro Hoist	Hoist (TBC)	Sunlift Micro and Birdie Compact (Both)
3		Existing booking for dates
4		For delivery this week
5		For collection this week		SORT COLUMN
6
7
8	Lodge Number	Check in Date	Nights	Check Out		Archive											3rd Party Equipment Requested	Availability	CheckDt1	Check1	Check2	Check3	Check4	Check5	Check6	Check7	Check8	Check9	Check10
9		44,799	3	44,802		FALSE							Yes					Check	3	0	0	0	0	0	0	2	0	0	0
10		44,799	7	44,806		FALSE				Yes			Yes					Check	4	0	0	0	3	0	0	2	0	0	0
11		44,802	4	44,806		FALSE				Yes								Check	2	0	0	0	3	0	0	0	0	0	0
12		44,799	3	44,802							Yes							Available	3	0	0	0	0	1	0	0	0	0	0
13		44,817	3	44,820						Yes								Available	1	0	0	0	3	0	0	0	0	0	0
14																			0	0	0	0	0	0	0	0	0	0	0
15																			0	0	0	0	0	0	0	0	0	0	0
16																			0	0	0	0	0	0	0	0	0	0	0
17																			0	0	0	0	0	0	0	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
Y9:Y17	Y9	=IFS(AND(I9="",COUNTA(N9:W9)=0),"",AND(Z9>=2,COUNTIFS(AA9:AJ9,">"&1)>0),"Check",TRUE,"Available")
Z9:Z17	Z9	=SUMPRODUCT((I9<$K$9:$K$17)*(K9>$I$9:$I$17))
AA9:AJ17	AA9	=SUMPRODUCT((N$9:N$17<>"")*(N$9:N$17=N9))
M9:M11	M9	=IF(J9="","",IF(K9<TODAY(),TRUE,FALSE))
K9:K13	K9	=IF(J9="","",I9+J9)