SUMPRODUCT and dates
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
---------------
Jacob Skaria
"Jacob Skaria" wrote:
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
---------------
Jacob Skaria
"Scott A" wrote:
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.
|