SUMIFS between dates in a 2D range

[Tarver]

I need to SUMIFS in a two-dimensional range, but SUMIFS doesn't do 2D. I've tried combining with an INDEX statement and a MATCH, but these aren't getting the right result, either.

I'm working with the following table:

I need to produce a report that shows Wendy and Harry's sales for March 2 - March 4th. My formula needs to look up the names of the salespeople and the dates of sales dynamically. They could change daily.

Assume that my Salesperson's name is in Cell X2, and my start date is in Y1 and the end date is in Z1. I need to total all of the sales included in and between the two dates.

Any suggestions on how I can accomplish this?

Thanks very much in advance!

BT

 

[steve the fish]

Why arent you just using the pivot out of interest?

 

[steve the fish]

Anyway ranges will need to be adjusted to suit the size of the pivot (or a range that it will never exceed)

=SUMIFS(INDEX($B$2:$F$6,MATCH($X2,$A$2:$A$6,0),0),$B$1:$F$1,">="&Y$1,$B$1:$F$1,"<="&Z$1)

 

[Tarver]

Why arent you just using the pivot out of interest?

The pivot has 248 entries, over a span of three months (currently) of data. I've got to report on the top 30 entries, for a specific range of dates (Yesterday, month to date, and year to date.) Then I have to report on the same data by a different category (but the principle is the same.) The pivot is too large and unwieldy for someone that just needs the top performance data.

 

[Tarver]

=SUMIFS(INDEX($B$2:$F$6,MATCH($X2,$A$2:$A$6,0),0),$B$1:$F$1,">="&Y$1,$B$1:$F$1,"<="&Z$1)

Thanks. I'll try this today.