Using SUMPRODUCT in place of COUNTIFS for multi column lookup

Hi guys.

All help HUGELY appreciated

I have a table:
A - B - C - D
Date - Name 1 - Name 2 - Fruit
1/2/2014 - Steve - Simon - Apple
2/3/2014 - Mark - Steve - Pear
10/3/2014 - Steve - Mark - Apple
1/2/2015 - Simon - Steve - Pear

I want a formula that will tell me how many times Steve's name appears in columns B or C during 2014 where the fruit is Apple = 2
IF there was only one column of names then I could do it easily with a COUNTIFS(A:A,>=date,A:A,<=date,B:B,"Simon",D:D,"Apple")

I think I need to replace the COUNTIFS with SUMPRODUCT, but I cant work out how to do it.

The formula features of "Steve", "Apple" and "Date" can either be hard written into the formula OR can reference another cell like (E1), I don't mind.

Thanks so much. The few trial and errors that I attempted failed entirely and you guys are always so knowledgeable.

Thanks everyone.
Almost there.
All formula options work IF I replace
*(YEAR(A2:A10)=2014))
with
A:A>1/1/2014

When I use the YEAR function I get a #VALUE error come back.

Why might that be?

Is there a way to remove the YEAR function and replace it with 'Greater than 1/1/2014 and less than 31/12/2014' ??

(Would have uploaded an example BUT was told in another thread that it is better to write it into the thread than to use uploads as it is more helpful for people searching for similar answers as it shows up in search engines this way.)

Try

=SUMPRODUCT(--(B2:C10="Steve")*(D2:D10="Apple")*(A2:A10)>="Startdate")*(A2:A10)<="Enddate"))

Startdate/Enddate are cells with your dates

Originally Posted by RoundaboutCJP

IF I replace
*(YEAR(A2:A10)=2014))
with
A:A>1/1/2014

When I use the YEAR function I get a #VALUE error come back.

Why might that be?

Most likely because you have a TEXT string in one of the cells, like a Header value in A1.

It's highly recommended to NOT use entire column references like A:A in a sumproduct formula.
Because sumproduct will perform it's functions on every single cell in the column, even the blank ones.
This creates 'alot' of unnecessary work, reducing sheet performance.

Restrict your ranges to the actual used area, and exlude the header row.

Ah ha. Thatll be it - column headers.
THanks for the advice and thanks all for the solutions!