Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Value (and Total) Based on Two Criteria
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Value (and Total) Based on Two Criteria
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 -- ***** birds of the same feather flock together.. "Skridlowe" wrote: 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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Value (and Total) Based on Two Criteria
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|