SUMPRODUCT and dates

[Scott A]

I am trying to use a SUMPRODUCT to query the country, then the city, and most
importantly, the date a trip falls between to determine a lodging rate.

Below is an extract from the gov't per diem tables. I have the country and
city worked out, but the result is adding all lodging rates that match. In
htis example, ANTIGUA AND BARUDA would return $320, MARTINIQUE would return
$255, regardless of dates. If I enter 20 Apr, I would want $154 or $122
rerspectively.

Country City From To Max Lodging
ANTIGUA & BARBUDA ALL CONTRACTOR FACILITIES 01-Jan 31-Dec 20
ANTIGUA & BARBUDA ANTIGUA AND BARBUDA 16-Apr 14-Dec 154
ANTIGUA & BARBUDA ANTIGUA AND BARBUDA 15-Dec 15-Apr 166
MARTINIQUE MARTINIQUE 16-Apr 14-Dec 122
MARTINIQUE MARTINIQUE 15-Dec 15-Apr 133


I have tried many things without success so I do appreciate any help.

 

[Jacob Skaria]

Hi Scott

With G2= ANTIGUA & BARBUDA
and G3 = 20-Apr

With your example the below formula retuns the total of the 1st two...which
falls during the date mentioned in G3...

=SUMPRODUCT(--(A1:A100=G2),--(C1:C100<=G3),--(D1100>=G3),E1:E100)

If this post helps click Yes

 

[T. Valko]

Hard to tell where one column ends and the next begins as far as the
country/city goes.

Try something like this:

=SUMPRODUCT(--(country_range="country"),--(city_range="city"),--(From_date<=some_date),--(To_date>=some_date),range_to_sum)

 

[Jacob Skaria]

With city in G3 and query date in G4

=SUMPRODUCT(--(A1:A100=G2),--(B1:B100=G3),--(C1:C100<=G4),--(D1100>=G4),E1:E100)


If this post helps click Yes