Lookup Value (and Total) Based on Two Criteria
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
"Skridlowe" wrote in message
...
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!
|