SUMPRODUCT and dates

  • Thread starter Thread starter Scott A
  • Start date Start date
S

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.
 
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),--(D1:D100>=G3),E1:E100)

If this post helps click Yes
 
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)
 
With city in G3 and query date in G4

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


If this post helps click Yes
 
Back
Top