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



[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1 Employee[/TD]
[TD]Category[/TD]
[TD]Date[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2 Thorndale[/TD]
[TD]ALL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Delivery[/TD]
[TD]DISPLAY 1/17/18 HERE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Purchase[/TD]
[TD]NOTHING IN CELL C3, LEAVE BLANK[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1 Employee[/TD]
[TD]Delivery[/TD]
[TD]Purchase[/TD]
[TD]ALL[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2 Smith[/TD]
[TD]2/21/18[/TD]
[TD]1/10/18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3 Thorndale[/TD]
[TD]1/17/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]








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.