Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT and dates
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT and dates
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT and dates
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) -- Biff Microsoft Excel MVP "Scott A" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SumProduct with dates | Excel Worksheet Functions | |||
SUMPRODUCT with dates | Excel Worksheet Functions | |||
sumproduct with dates | New Users to Excel | |||
sumproduct & dates | Excel Worksheet Functions | |||
sumproduct with dates | Excel Worksheet Functions |