Populate date within range using criteria

[TXTX]

Having trouble creating a formula that will populate "pay date" based on first criteria "color" and date that falls within date range. Is there a simple way to do this? For exampling hoping to achieve that the formula will start by identifying "BLUE" in Color column, Then return value from Pay Date column that falls between 1/1/2007 and 12/31/2007 (i.e. 5/1/2007)

 

[Fluff]

Hi & welcome to MrExcel.
Will there only ever be one date that matches all criteria?

 

[TXTX]

That is correct. From my dataset, there will never be more than one date that 1) falls between the date range and 2) meets the color criteria.

 

[Fluff]

Ok, how about

=SUMIFS(C:C,A:A,E3,C:C,">="&F1,C:C,"<="&F2)

 

[TXTX]

Correction, there never be more than one date and there may also be no date within the range. If the date range was changed to 1/1/22 and 12/31/22, the result is "0", but is there a way for the value to return "blank" instead of zero?

I currently have =IF(SUMIFS(C:C,A:A,E3,C:C,">="&F1,C:C,"<="&F2)=0,"",SUMIFS(C:C,A:A,E3,C:C,">="&F1,C:C,"<="&F2)). But I wonder if there's another formula that's not as long but achieves the same result.

 

[Fluff]

How about

=iferror(1/(1/SUMIFS(C:C,A:A,E3,C:C,">="&F1,C:C,"<="&F2)),"")

 

[TXTX]

Brilliant!

 

[Fluff]

You're welcome & thanks for the feedback.