Adding Date Range Criteria to If Statement

[mtyrrell13]

can someone help me understand where my syntax error is in the below formula. I am trying to add the third if statement which is a date range criteria. when I enter it it says too many arguments entered for this function. I suspect I have an extra comma somewhere but I can not seem to see any error with it

Formula =IFERROR(INDEX('2H Pipeline'!C2:C165,MATCH(LARGE(IF('2H Pipeline'!E2:E165=U2,IF('2H Pipeline'!B2:B165=B7,IF('2H Pipeline'!D2:D165,">="&"7/1/2019",'2H Pipeline'!D2:D165,"<="&"9/30/2019")),B6),'2H Pipeline'!P2:P165,0)),"")

 

[Mentor82]

Hi,
Here you have syntax error - excel stored daye as a number 01-01-1900 i 1 and so on.
Pipeline'!D2:D165,">="&"7/1/2019"
Should be
Pipeline'!D2:D165,">="& Date(2019,7,1)

 

[Mentor82]

Hi again
Here you should change too.
Pipeline'!D2:D165,"<="&"9/30/2019")) chsnge to
Pipeline'!D2:D165,"<="&Date(2019,9,30))),

 

[mtyrrell13]

that is certainly one way to do the date criteria, but hard coding the date as I had it also works as I have en entire spreadsheet using that format. out of Curiosity I converted it to the Date formula way and same result "too many arguments for this function"

Formula =IFERROR(INDEX('2H Pipeline'!C2:C165,MATCH(LARGE(IF('2H Pipeline'!E2:E165=U2,IF('2H Pipeline'!B2:B165=B7,IF('2H Pipeline'!D2:D165,">="&Date(2019,7,1) ,'2H Pipeline'!D2:D165,"<="&Date(2019,9,30))),B6),'2H Pipeline'!P2:P165,0)),"")

 

[Mentor82]

Hi,
Try this. I noticed.that.you ghave one if without and where you placed a range of dates.

Formula = =IFERROR(INDEX('2H Pipeline'!C2:C165,MATCH(LARGE(IF('2H Pipeline'!E2:E165=U2,IF('2H Pipeline'!B2:B165=B7,IF(AND('2H Pipeline'!D2:D165,">="& Date(2019,7,1),'2H Pipeline'!D2:D165,"<="&Date(2019,9,30)),B6)))),'2H Pipeline'!P2:P165,0)),"")

 

[mtyrrell13]

Thank you for your help, I tried this option and it says too few arguments for this function vs. too many arguments for this function

 

[Mentor82]

Hi,
What do you want to achieve? You want to return value from column C if all the following conditions are met:
max in col E = value U2 and max in col B = value B7 and date in col D is between 01.07.2019 and 30.09.2019, is that correct? If so your formulae is wrong but before I correct it I need to know what you need as result.

 

[mtyrrell13]

Thanks again for your patience and help

Formula=IFERROR(INDEX('2H Pipeline'!C2:C166,MATCH(LARGE(IF('2H Pipeline'!E2:E166=U2,IF('2H Pipeline'!B2:B166=G7,'2H Pipeline'!P2:P166)),G6-1),'2H Pipeline'!P2:P166,0)),"")

so the formula above is a copy of one that works as expected and let me explain what it is doing, and then I will explain what I want to change

1. it is indexing the full data set in the pipeline tab
2. then it is Matching deals on that tab that meet a certain criteria (cell U2 = a Person, Cell G7 = a Deal Status such as Stretch-Q4)
3. a separate cell sums a total count of all the deals that meet the same criteria above
then I am just returning the largest tcv in that list and cell G6 is the largest, G6-1 next largest, and so on

the key portion I want to change is the #2 above

since the pipeline tab contains deals that span the full year and into next year I want to limit the number of customized "deal Status" I have to define, i.e Stretch-Q3, Stretch-Q4, Stretch-Q1-2020, etc.. Instead since I have the date I want to add a third criteria and leave G7 as simply "Stretch" and then based on the date determine where I put it as that drives the "quarter" designation

so my plan was to just simply add another IF statement to include the date criteria