Lookup Value (and Total) Based on Two Criteria

[Skridlowe]

I have a spreadsheet with a date range (of either one day or several days),
three people with distinct ID numbers, and the numbers those people enter for
a specific date within the range.

I need to lookup the NUMBER, based on DATE range and ID and total the
NUMBERs, based on these criteria. Given the following info:

DATE ID NUMBER
8-Jan 4501 2
8-Jan 4502 3
8-Jan 4503 4
9-Jan 4501 6
9-Jan 4502 3
9-Jan 4503 9
10-Jan 4501 0
10-Jan 4502 4
10-Jan 4503 8
11-Jan 4501 10
11-Jan 4502 7
11-Jan 4503 3
12-Jan 4501 2
12-Jan 4502 6
12-Jan 4503 8

And a date range of either
8-Jan ID NUMBER
4501 number for this date
4502 number for this date
4503 number for this date

OR
8-Jan 12-Jan ID NUMBER
4501 sum of all numbers for date range
4502 sum of all numbers for date range
4503 sum of all numbers for date range

Which if formulated correctly, should give the following:

8-Jan 4501 = 2
8-Jan 4501 = 3
8-Jan 4501 = 4

8-Jan thru 12-Jan 4501 = 20
8-Jan thru 12-Jan 4502 = 23
8-Jan thru 12-Jan 4503 = 32

Any help formuilating this in Excel would be of great help!
Thanks!

 

[T. Valko]

Assume your data is in the range A2:A16

Use cells to hold your criteria:

E2 = start date
F2 = end date
G2 = ID number

For a single date (F2 end date would be empty):

=SUMPRODUCT(--(A$2:A$16=E$2),--(B$2:B$16=G2),C$2:C$16)

For a date range (F2 would now contain the end date):

=SUMPRODUCT(--(A$2:A$16>=E$2),--(A$2:A$16<=F2),--(B$2:B$16=G2),C$2:C$16)

Biff

 

[driller]

it may look like a calculator

assuming your data are on columns A,B,C..A2:C16
then on Column D,E,F.G for 3 ROWS
D2:4=Date1 e.g. 08-Jan
E2:4=Date 2 e.g. 12-Jan
F2 = 4501;
F3=4502 ;
F4=4503..
G2=SUMPRODUCT(($A$2:$A$16>=D2)*($A$2:$A$16<=E2)*($B$2:$B$16=F2),$C$2:$C$16)

COPY PASTE DOWN TO G4

for One Date lookup, just enter the same date on D2 and E2
D2:4=Date1 e.g. 08-Jan
E2:4=Date 2 e.g. 08-Jan

Adjust to suit..

regards

 

[Herbert Seidenberg]

Pivot Table requires no formulas at all.
For the 8-Jan table:
Layout: Row = DATE, ID; Data = Sum of NUMBER

From the DATE dropdown list, select only 8-Jan

For the 8-Jan thru 12-Jan table:
Layout: Row = ID; Data = Sum of NUMBER