If(or formula-array

[samyscraps01]

Good Afternoon,

I would like some help on a formula for IF(OR. I have attached the spreadsheet for reference. I would like the formula to do two things:

1. when the category drop-down menu is on ALL. I would like it to display all the results on cell C7 and C8.
2. If a date is not available because it is is blank rather than having it return a random date as it has been doing. I would like it to just stay blank.

I have been trying to modify the formula but it is not working well even though I added the all into it. Also, does the formula need to become an array for it to list all possible dates once I select all?

For reference, I am not going to be posting this on other sites.

Thanks.
http://IFOR.JPG

A	B	C	D
1 Employee	Category	Date
2 Thorndale	ALL
3
7	Delivery	DISPLAY 1/17/18 HERE
8	Purchase	NOTHING IN CELL C3, LEAVE BLANK

<tbody>
</tbody>

What i would like is two things.

1. when I select a given employee and I select ALL from the Category drop-down menu then give me all the dates at once associated with that employee. They should populate in Cells C7 and C8. This is coming from the informatoin on tab Y of the same spreadsheet.

2. If there is nothing in the cell, then return a blank rather than a random date.








Main Spreadsheet Formula

=IF(OR($A$2="",$B$2="",SUMPRODUCT((Sheet1!A$2:A$10=$A$2)*(Sheet1!B$1:D$1=$B$2)+($A$5="ALL")*(Sheet1!B$2:D$10))=0),"",SUMPRODUCT((Sheet1!A$2:A$10=$A$2)*(Sheet1!B$1:D$1=$B$2)*(Sheet1!B$2:D$10))) FORMULA IN CELL C2


=IF(OR($B$2="ALL", $B$2="ALL"),"Delivery","") FORMULA IN CELL C7, THESE FORMULAS ONLY APPEAR WHEN ALL IN CELL B2 IS SELECTED

=IF(OR($B$2="ALL", $B$2="ALL"),"Purchase","") FORMULA IN CELL C8, " "

TAB Y

A	B	C	D
1 Employee	Delivery	Purchase	ALL
2 Smith	2/21/18	1/10/18
3 Thorndale	1/17/18

<tbody>
</tbody>

IF CELL IS BLANK LIKE IN C3 DO NOT ENTER A DATE, IT'S BEEN INPUTTING A DATE OF 1/0/1900. NOT SURE WHY BUT I WANT IT TO STAY BLANK.