Multiple Criteria in If function Returns error

[arijitirf]

Hi!
I need to calculate "Full Day" or "Half Day" in Sheet 2 at D3.

Sheet 1 having data from A3:D504, where

A3:A504 having "Date" e.g. 03-03-2024

C3:C504 having "Category" like Lunch, Hi Tea, Dinner, Setup, PMT, AMT etc

D3:D504 having "Space" like ATR, ATR1, ATR2, ATR3, DRM etc.

Full Day" or "Half Day" calculation in Sheet 2 at D3 will be depending upon

Criteria 1: If Sheet 2 at C1 having Date Range similar to Date in Sheet 1 A3:A504

Criteria 2: If Sheet 2 at C2 having "Category" similar to "Category" in Sheet 1 C3:C504 (only Lunch then "Half Day", only Hi Tea then "Half Day", only Dinner then "Half Day", Lunch+Hi Tea then "Half Day", Hi Tea+Dinner then " Half Day", Lunch+Hi+Dinner then "Full Day", Lunch+Dinner then "Full Day", only Setup then "Half Day", only AMT then "Half Day"

Criteria 3: If Sheet 2 at C3 having "Space" similar to "Space" in Sheet 1 D3:D504

Criteria 1 + Criteria 2 + Criteria 3 = Full Day or Half Day at D3 in Sheet 2

I have tried with multiple nested If Function however, my only works for first date in Sheet 2 at C1

Thanks in advance

 

[etaf]

a sample sheet would help a lot here with expected results

having "Space" similar to "Space"

when you say similar - what does that mean exactly

Do you need a lookup - or will the result you want always match the same row on sheet1 to sheet2 ?
if identical dates etc in same row - then an IF may work
Otherwise maybe an index/match with an IF to return halff/full

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[arijitirf]

Kindly find the link of sample spreadsheet below

Loading Google Sheets

I need a lookup function to calculate if given criteria is matched. I have mentioned it in the Sample Spreadsheet. Hope the remarks mentioned in the spreadsheet will be helpful to you.

Thank you for your prompt response.

 

[etaf]

thanks but still not following

What version of excel are you using - would be worth updating your profile to show version as solutions will be dependant on what version you have and therefore what functions available.
Excel Versions are like: 2003/2007/2010/2013/2016/2019/2021/365 , also rather than show more than 1 version if you have them , which version you will be using the solution for. As an awful lot of new functions have now been added to the newer versions


counitfs() with an IF() may work here

so we are looking up and matching date - AND also looking up

"Duration" in C3 will Calculate "Full Day" if date in Sheet 1 Range A2:A11 matches with Date at A3 in Sheet 2, Category if falls under same date as A3 and category is either (Lunch+Hi Tea+Dinner) or (only Setup) or (Lunch+Dinner)

category
to be 1 of the following
(Lunch+Hi
Tea+Dinner)
(only Setup)
(Lunch+Dinner)

in your example
sheet 1 all you have is
So-V-Lunch
Co-V-Hi Tea
So-NV-Dinner
So-V-Lunch
Co-V-Dinner
So-NV-Hi Tea
Setup
Co-V-Lunch
-
Co-NV-Dinner
AMT
Co-NV-Dinner

so i'm not sure what you are looking for in your example

can you explain how
(Lunch+Hi
Tea+Dinner)
(only Setup)
(Lunch+Dinner)

would convert to find

So-V-Lunch
Co-V-Hi Tea
So-NV-Dinner
So-V-Lunch
Co-V-Dinner
So-NV-Hi Tea
Setup
Co-V-Lunch
-
Co-NV-Dinner
AMT
Co-NV-Dinner

then a countifs() should work - within an IF

'=(countifs(Sheet1!A2:A13,Sheet2!A3,Sheet1!C2:C13,{now we can have a list of Ors} )
so if that count is >0 , then TRUE and so FULL DAY

IF( countifs(Sheet1!A2:A13,Sheet2!A3,Sheet1!C2:C13,{now we can have a list of Ors} >0 , "full day", IF( criteria for half day

Also posted as XL2BB here , in case that helps other members who are not prepared to go to shares like google sheets

sheet1

Space Duration Calculator.xlsx
	A	B	C	D
1	Date Range	Date Hd.	Category	Space
2	3/1/24	A	So-V-Lunch	ATR
3	3/1/24	A	Co-V-Hi Tea	ATR
4	3/1/24	A	So-NV-Dinner	ATR
5	3/2/24	B	So-V-Lunch	ATR
6	3/3/24	A	Co-V-Dinner	ATR
7	3/3/24	A	So-NV-Hi Tea	ATR
8	3/4/24	C	Setup	ATR
9	3/5/24	B	Co-V-Lunch	ATR
10	3/5/24	B	-	ATR
11	3/5/24	B	Co-NV-Dinner	ATR
12	3/6/24	C	AMT	ATR
13	3/6/24	C	Co-NV-Dinner	ATR
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
C2:C11	List	=$R$2:$R$21

Sheet2

Space Duration Calculator.xlsx
	A	B	C	D	E	F
1			ATR
2	Date Range	Date Hd.	Duration	Min. Accrual	Rev. Generated	Remarks
3	3/1/24	A	=if(countifs(Sheet1!A2:A13,Sheet2!A3,Sheet1!C2:C13,{now we can have a list of Ors} )			"Duration" in C3 will Calculate "Full Day" if date in Sheet 1 Range A2:A11 matches with Date at A3 in Sheet 2, Category if falls under same date as A3 and category is either (Lunch+Hi Tea+Dinner) or (only Setup) or (Lunch+Dinner)
4	3/2/24	B	Half Day			"Duration" in C3 will Calculate "Half Day" if date in Sheet 1 Range A2:A11 matches with Date at A3 in Sheet 2, Category if falls under same date as A3 and category is either (only Lunch) or (only Hi Tea) or (only Dinner) or (Lunch+HiTea) or (Hi Tea+Dinner) or (only AMT) or (only PMT) or (AMT+Dinner) or (AMT+Lunch)
5	3/3/24	A	Half Day			"Duration" in C3 will Calculate "Half Day" if date in Sheet 1 Range A2:A11 matches with Date at A3 in Sheet 2, Category if falls under same date as A3 and category is either (only Lunch) or (only Hi Tea) or (only Dinner) or (Lunch+HiTea) or (Hi Tea+Dinner) or (only AMT) or (only PMT) or (AMT+Dinner) or (AMT+Lunch)
6	3/4/24	C	Full Day			"Duration" in C3 will Calculate "Full Day" if date in Sheet 1 Range A2:A11 matches with Date at A3 in Sheet 2, Category if falls under same date as A3 and category is either (Lunch+Hi Tea+Dinner) or (only Setup) or (Lunch+Dinner)
7	3/5/24	B	Full Day			"Duration" in C3 will Calculate "Full Day" if date in Sheet 1 Range A2:A11 matches with Date at A3 in Sheet 2, Category if falls under same date as A3 and category is either (Lunch+Hi Tea+Dinner) or (only Setup) or (Lunch+Dinner)
8	3/6/24	C	Half Day			"Duration" in C3 will Calculate "Half Day" if date in Sheet 1 Range A2:A11 matches with Date at A3 in Sheet 2, Category if falls under same date as A3 and category is either (only Lunch) or (only Hi Tea) or (only Dinner) or (Lunch+HiTea) or (Hi Tea+Dinner) or (only AMT) or (only PMT) or (AMT+Dinner) or (AMT+Lunch)
9	3/7/24
10	3/8/24
11	3/9/24
12	3/10/24
13	3/11/24
14	3/12/24
15	3/13/24
16	3/14/24
17	3/15/24
18	3/16/24
19	3/17/24
20	3/18/24
21	3/19/24
22	3/20/24
23	3/21/24
24	3/22/24
25	3/23/24
26	3/24/24
27	3/25/24
28	3/26/24
29	3/27/24
30	3/28/24
31	3/29/24
32	3/30/24
33	3/31/24
Sheet2

Cell Formulas
Range	Formula
B3:B32	B3	=IF(ISERROR(VLOOKUP(A3,Sheet1!A2:B11,2,0)),"",VLOOKUP(A3,Sheet1!A2:B11,2,0))

 

[arijitirf]

I'm using Office 16.

Since my company provided Laptop has some restriction, I am unable to provide XL2BB.

Full Day or Half Day Calculation is based upon the Date at A3 In Sheet 2, Space is ATR and below Criteria

For Half Day: If Sheet 1 C2:C13 having string "Lunch" or "Hi Tea" or "Dinner" or Lunch&Hi Tea or Hi Tea&Dinner

For Full Day: if Sheet 1 C2:C13 having string "Lunch&Dinner" or "Lunch&Hi Tea&Dinner" or "Setup"

For example: Excel will search Date mention in Sheet 2 at A3 within Sheet 1 A2:A13 then it will search for Space mention in Sheet 2 at C1 within Sheet 1 D2:D13 If matched thwn it will search for how many category is used within the search.

if there are 3 Rows under same Date and same Space then it will take string of Lunch, Hi Tea & Dinner in above way to determine the expected result. Like 1st row having So-V-Lunch, 2nd row having Co-V-Hi Tea and 3rd row having So-NV-Dinner then it will add like Lunch&HiTea&Dinner and expected result will be Full Day.

If ther is two row, one having Co-V-Dinner and another having So-V-Hi Tea then it will add like HiTea&Dinner and expected result will be Half Day

 

[etaf]

ok, thanks - still trying to work out exactly
FULL DAY
lunch and dinner = full day
Setup = full day
so as we already have Lunch and Dinner - does that not mean hi tea is redundant
since "Lunch&Dinner" or "Lunch&Hi Tea&Dinner"
Lunch & dinner would mean full day anyway - so Lunch&Hi Tea&Dinner" is irrelevant

just trying to see how best to code this
maybe with a countifs()

=OR(AND(COUNTIFS(Sheet1!$C$2:$C$13,"*"&"lunch"&"*",Sheet1!$A$2:$A$13,A3)>0,COUNTIFS(Sheet1!$C$2:$C$13,"*"&"dinner"&"*",Sheet1!$A$2:$A$13,A3)),COUNTIFS(Sheet1!$C$2:$C$13,"setup",Sheet1!$A$2:$A$13,A3))

will be a full day

=IF(OR(AND(COUNTIFS(Sheet1!$C$2:$C$13,"*"&"lunch"&"*",Sheet1!$A$2:$A$13,A3)>0,COUNTIFS(Sheet1!$C$2:$C$13,"*"&"dinner"&"*",Sheet1!$A$2:$A$13,A3)),COUNTIFS(Sheet1!$C$2:$C$13,"setup",Sheet1!$A$2:$A$13,A3)),"Full Day","")

and will that mean That everything else is half day - no matter what is in the text ???

trying to simplify as much as possible

see column H
we also need to add in the criteria for space , but thats straightforward , as just another part of the countifs()

COUNTIFS(Sheet1!$C$2:$C$13,"*"&"lunch"&"*",Sheet1!$A$2:$A$13,A3, Sheet1!$D$2:$D$13, "ATR")>0

Space Duration Calculator.xlsx
	A	B	C	D	E	F	G	H
1			ATR
2	Date Range	Date Hd.	Duration	Min. Accrual	Rev. Generated	Remarks
3	3/1/24	A	Full Day			"Duration" in C3 will Calculate "Full Day" if date in Sheet 1 Range A2:A11 matches with Date at A3 in Sheet 2, Category if falls under same date as A3 and category is either (Lunch+Hi Tea+Dinner) or (only Setup) or (Lunch+Dinner)		Full Day
4	3/2/24	B	Half Day			"Duration" in C3 will Calculate "Half Day" if date in Sheet 1 Range A2:A11 matches with Date at A3 in Sheet 2, Category if falls under same date as A3 and category is either (only Lunch) or (only Hi Tea) or (only Dinner) or (Lunch+HiTea) or (Hi Tea+Dinner) or (only AMT) or (only PMT) or (AMT+Dinner) or (AMT+Lunch)
5	3/3/24	A	Half Day			"Duration" in C3 will Calculate "Half Day" if date in Sheet 1 Range A2:A11 matches with Date at A3 in Sheet 2, Category if falls under same date as A3 and category is either (only Lunch) or (only Hi Tea) or (only Dinner) or (Lunch+HiTea) or (Hi Tea+Dinner) or (only AMT) or (only PMT) or (AMT+Dinner) or (AMT+Lunch)
6	3/4/24	C	Full Day			"Duration" in C3 will Calculate "Full Day" if date in Sheet 1 Range A2:A11 matches with Date at A3 in Sheet 2, Category if falls under same date as A3 and category is either (Lunch+Hi Tea+Dinner) or (only Setup) or (Lunch+Dinner)		Full Day
7	3/5/24	B	Full Day			"Duration" in C3 will Calculate "Full Day" if date in Sheet 1 Range A2:A11 matches with Date at A3 in Sheet 2, Category if falls under same date as A3 and category is either (Lunch+Hi Tea+Dinner) or (only Setup) or (Lunch+Dinner)		Full Day
8	3/6/24	C	Half Day			"Duration" in C3 will Calculate "Half Day" if date in Sheet 1 Range A2:A11 matches with Date at A3 in Sheet 2, Category if falls under same date as A3 and category is either (only Lunch) or (only Hi Tea) or (only Dinner) or (Lunch+HiTea) or (Hi Tea+Dinner) or (only AMT) or (only PMT) or (AMT+Dinner) or (AMT+Lunch)
9	3/7/24
10	3/8/24
11	3/9/24
12	3/10/24
13	3/11/24
Sheet2

Cell Formulas
Range	Formula
B3:B13	B3	=IF(ISERROR(VLOOKUP(A3,Sheet1!A2:B11,2,0)),"",VLOOKUP(A3,Sheet1!A2:B11,2,0))
H3:H13	H3	=IF(OR(AND(COUNTIFS(Sheet1!$C$2:$C$13,"*"&"lunch"&"*",Sheet1!$A$2:$A$13,A3)>0,COUNTIFS(Sheet1!$C$2:$C$13,"*"&"dinner"&"*",Sheet1!$A$2:$A$13,A3)),COUNTIFS(Sheet1!$C$2:$C$13,"setup",Sheet1!$A$2:$A$13,A3)),"Full Day","")

will only be on dropbox a few days after solution

Space Duration Calculator - ETAF.xlsx

Shared with Dropbox

www.dropbox.com

 

[etaf]

so working on half day
if full day is not met
then

=OR(COUNTIFS(Sheet1!$C$2:$C$13,"*"&"lunch"&"*",Sheet1!$A$2:$A$13,A3,Sheet1!$D$2:$D$13,"atr")>0,COUNTIFS(Sheet1!$C$2:$C$13,"*"&"dinner"&"*",Sheet1!$A$2:$A$13,A3,Sheet1!$D$2:$D$13,"atr"),COUNTIFS(Sheet1!$C$2:$C$13,"*"&"hi tea"&"*",Sheet1!$A$2:$A$13,A3,Sheet1!$D$2:$D$13,"atr"))

is just if there is dinner or lunch or hi tea
as we have already tested for Full day - then this must be half a day

=IF(OR(AND(COUNTIFS(Sheet1!$C$2:$C$13,"*"&"lunch"&"*",Sheet1!$A$2:$A$13,A3,Sheet1!$D$2:$D$13,"atr")>0,COUNTIFS(Sheet1!$C$2:$C$13,"*"&"dinner"&"*",Sheet1!$A$2:$A$13,A3,Sheet1!$D$2:$D$13,"atr")),COUNTIFS(Sheet1!$C$2:$C$13,"setup",Sheet1!$A$2:$A$13,A3,Sheet1!$D$2:$D$13,"atr")),"Full Day",IF(OR(COUNTIFS(Sheet1!$C$2:$C$13,"*"&"lunch"&"*",Sheet1!$A$2:$A$13,A3,Sheet1!$D$2:$D$13,"atr")>0,COUNTIFS(Sheet1!$C$2:$C$13,"*"&"dinner"&"*",Sheet1!$A$2:$A$13,A3,Sheet1!$D$2:$D$13,"atr"),COUNTIFS(Sheet1!$C$2:$C$13,"*"&"hi tea"&"*",Sheet1!$A$2:$A$13,A3,Sheet1!$D$2:$D$13,"atr")),"Half day",""))

Space Duration Calculator - ETAF - Half and full day.xlsx
	A	B	C	D	E	F	G	H	I
1			ATR
2	Date Range	Date Hd.	Duration	Min. Accrual	Rev. Generated	Remarks
3	3/1/24	A	Full Day			"Duration" in C3 will Calculate "Full Day" if date in Sheet 1 Range A2:A11 matches with Date at A3 in Sheet 2, Category if falls under same date as A3 and category is either (Lunch+Hi Tea+Dinner) or (only Setup) or (Lunch+Dinner)		Full Day	TRUE
4	3/2/24	B	Half Day			"Duration" in C3 will Calculate "Half Day" if date in Sheet 1 Range A2:A11 matches with Date at A3 in Sheet 2, Category if falls under same date as A3 and category is either (only Lunch) or (only Hi Tea) or (only Dinner) or (Lunch+HiTea) or (Hi Tea+Dinner) or (only AMT) or (only PMT) or (AMT+Dinner) or (AMT+Lunch)		Half day	TRUE
5	3/3/24	A	Half Day			"Duration" in C3 will Calculate "Half Day" if date in Sheet 1 Range A2:A11 matches with Date at A3 in Sheet 2, Category if falls under same date as A3 and category is either (only Lunch) or (only Hi Tea) or (only Dinner) or (Lunch+HiTea) or (Hi Tea+Dinner) or (only AMT) or (only PMT) or (AMT+Dinner) or (AMT+Lunch)		Half day	TRUE
6	3/4/24	C	Full Day			"Duration" in C3 will Calculate "Full Day" if date in Sheet 1 Range A2:A11 matches with Date at A3 in Sheet 2, Category if falls under same date as A3 and category is either (Lunch+Hi Tea+Dinner) or (only Setup) or (Lunch+Dinner)		Full Day	FALSE
7	3/5/24	B	Full Day			"Duration" in C3 will Calculate "Full Day" if date in Sheet 1 Range A2:A11 matches with Date at A3 in Sheet 2, Category if falls under same date as A3 and category is either (Lunch+Hi Tea+Dinner) or (only Setup) or (Lunch+Dinner)		Full Day	TRUE
8	3/6/24	C	Half Day			"Duration" in C3 will Calculate "Half Day" if date in Sheet 1 Range A2:A11 matches with Date at A3 in Sheet 2, Category if falls under same date as A3 and category is either (only Lunch) or (only Hi Tea) or (only Dinner) or (Lunch+HiTea) or (Hi Tea+Dinner) or (only AMT) or (only PMT) or (AMT+Dinner) or (AMT+Lunch)		Half day	TRUE
9	3/7/24								FALSE
10	3/8/24								FALSE
11	3/9/24								FALSE
12	3/10/24								FALSE
13	3/11/24								FALSE
14	3/12/24								FALSE
15	3/13/24								FALSE
16	3/14/24
17	3/15/24
18	3/16/24
Sheet2

Cell Formulas
Range	Formula
I3:I15	I3	=OR(COUNTIFS(Sheet1!$C$2:$C$13,"*"&"lunch"&"*",Sheet1!$A$2:$A$13,A3,Sheet1!$D$2:$D$13,"atr")>0,COUNTIFS(Sheet1!$C$2:$C$13,"*"&"dinner"&"*",Sheet1!$A$2:$A$13,A3,Sheet1!$D$2:$D$13,"atr"),COUNTIFS(Sheet1!$C$2:$C$13,"*"&"hi tea"&"*",Sheet1!$A$2:$A$13,A3,Sheet1!$D$2:$D$13,"atr"))
H3:H17	H3	=IF(OR(AND(COUNTIFS(Sheet1!$C$2:$C$13,"*"&"lunch"&"*",Sheet1!$A$2:$A$13,A3,Sheet1!$D$2:$D$13,"atr")>0,COUNTIFS(Sheet1!$C$2:$C$13,"*"&"dinner"&"*",Sheet1!$A$2:$A$13,A3,Sheet1!$D$2:$D$13,"atr")),COUNTIFS(Sheet1!$C$2:$C$13,"setup",Sheet1!$A$2:$A$13,A3,Sheet1!$D$2:$D$13,"atr")),"Full Day",IF(OR(COUNTIFS(Sheet1!$C$2:$C$13,"*"&"lunch"&"*",Sheet1!$A$2:$A$13,A3,Sheet1!$D$2:$D$13,"atr")>0,COUNTIFS(Sheet1!$C$2:$C$13,"*"&"dinner"&"*",Sheet1!$A$2:$A$13,A3,Sheet1!$D$2:$D$13,"atr"),COUNTIFS(Sheet1!$C$2:$C$13,"*"&"hi tea"&"*",Sheet1!$A$2:$A$13,A3,Sheet1!$D$2:$D$13,"atr")),"Half day",""))
B3:B18	B3	=IF(ISERROR(VLOOKUP(A3,Sheet1!A2:B11,2,0)),"",VLOOKUP(A3,Sheet1!A2:B11,2,0))

another version on dropbox

Space Duration Calculator - ETAF - Half and full day.xlsx

Shared with Dropbox

www.dropbox.com

 

[arijitirf]

Superb! Thank you so much. Your formula works like a magic. Exactly what I wanted. I beg your pardon for my poor skill to make you understand what I wanted. Thank you once again.

 

[etaf]

you are welcome

I beg your pardon for my poor skill to make you understand what I wanted

no problem at all , fully understandable - hence all the questions so we get to what you want - happens a lot, so no worries

But i would put your version of excel on profile for any future questions you may have - lots of new functions in later versions , will NOT work in 2016 version