Searching Multiple Columns for Multiple Variables (Google Sheet)

EXCEL FIENDS!

I have an Excel sheet. I want it to sum the hours worked (Column J) for a particular person based on multiple criteria:

1. If the person's name (A27) appears in any of 3 columns (D,E, or F)

2. If the row falls within a certain DATE range (between BO1 and BO2) - (Column B)

3. If the description of the event says "Apples" OR "Oranges" (Column C)

This works searching only searching Column D:

=SUMIFS('Sheet 2'!$J:$J,'Sheet 2'!$D:$D,$A27,'Sheet 2'!$B:$B,">="&BO$1,'Sheet 2'!$B:$B,"<="&BO$2,'Sheet 2'!$C:$C,"Apples") + SUMIFS('Sheet 2'!$J:$J,'Sheet 2'!$D:$D,$A27,'Sheet 2'!$B:$B,">="&BO$1,'Sheet 2'!$B:$B,"<="&BO$2,'Sheet 2'!$C:$C,"Oranges")

...then I have to copy+paste it in triplicate to search E and F columns:

+SUMIFS('Sheet 2'!$J:$J,'Sheet 2'!$E:$E,$A27,'Sheet 2'!$B:$B,">="&BO$1,'Sheet 2'!$B:$B,"<="&BO$2,'Sheet 2'!$C:$C,"Apples") + SUMIFS('Sheet 2'!$J:$J,'Sheet 2'!$E:$E,$A27,'Sheet 2'!$B:$B,">="&BO$1,'Sheet 2'!$B:$B,"<="&BO$2,'Sheet 2'!$C:$C,"Oranges")

+ SUMIFS('Sheet 2'!$J:$J,'Sheet 2'!$F:$F,$A27,'Sheet 2'!$B:$B,">="&BO$1,'Sheet 2'!$B:$B,"<="&BO$2,'Sheet 2'!$C:$C,"Apples") + SUMIFS('Sheet 2'!$J:$J,'Sheet 2'!$F:$F,$A27,'Sheet 2'!$B:$B,">="&BO$1,'Sheet 2'!$B:$B,"<="&BO$2,'Sheet 2'!$C:$C,"Oranges")

I want to know if:

A) Can I somehow have it search all 3 column for Names at once instead of creating this giant clusterfunk for each individual column.

B) Why on EARTH can't I get it to look at Apples "OR" Oranges in a single formula like this (searching just Column D for names right now):
=SUM(SUMIFS('Sheet 2'!$J:$J,'Sheet 2'!$C:$C,{"Apples","Oranges"},'Sheet 2'!$D:$D,$A27,'Sheet 2'!$B:$B,">="&BO$1,'Sheet 2'!$B:$B,"<="&BO$2))

^^ That formula gives me only Apples and no Oranges. If I flip the values in the formula around I get Oranges but no Apples. What the heck?

I have an example link, not an attachment file and having trouble using "Manage Attachments" to share a link to the Worksheet:


Link is: docs.google.com/spreadsheets/d/1tBAA8AdEOGkh2_lI0nlkV3390bqALsV1VzrhVhrTy1A/edit#gid=0

File is also attached!!