Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple Lookup Criteria (revisited)
Hi All,
I'm using the following formula to return data in a sheet: =SUMPRODUCT(--(Demand1!$A$1:$A$9731=$A21),(--Demand1!$B$1:$B$9731=G$19),(--Demand1!$D$1:$D$9731=$B$17),(Demand1!$C$1:$C$9731) ) In the sheet with the formula, I have part numbers down column A, dates across rows (19 in this case), and another text qualifier in B17. In sheet "Demand1" I have 4 columns - A has part numbers, B has dates, C has quantity (the value I want returned), and D has the text qualifier. My problem is that the formula sometimes works and sometimes doesn't - it is very tempermental. I've tried matching all the formating, and still sometimes it does not work. Anyone have any ideas? Thanks, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple Lookup Criteria (revisited)
There is a slight danger in your approach.
You are setting up criteria on cols A,B,D and pulling data from col C. If more than one row matches the criteria, you will get the sum of the passing col C values rather than just a single col C value. -- Gary''s Student - gsnu200814 "Cam1234" wrote: Hi All, I'm using the following formula to return data in a sheet: =SUMPRODUCT(--(Demand1!$A$1:$A$9731=$A21),(--Demand1!$B$1:$B$9731=G$19),(--Demand1!$D$1:$D$9731=$B$17),(Demand1!$C$1:$C$9731) ) In the sheet with the formula, I have part numbers down column A, dates across rows (19 in this case), and another text qualifier in B17. In sheet "Demand1" I have 4 columns - A has part numbers, B has dates, C has quantity (the value I want returned), and D has the text qualifier. My problem is that the formula sometimes works and sometimes doesn't - it is very tempermental. I've tried matching all the formating, and still sometimes it does not work. Anyone have any ideas? Thanks, |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple Lookup Criteria (revisited)
I kind of figured it out, but still need some help. The problem was that the
dates are both formatted mmm-yy (eg, Nov-08), but the dates on the first sheet are all for the first of the month, and the dates on the second sheet are dates all over the month. Is there anyway to match it just by month and year and ignoring the day of the month? Thanks, "Cam1234" wrote: Hi All, I'm using the following formula to return data in a sheet: =SUMPRODUCT(--(Demand1!$A$1:$A$9731=$A21),(--Demand1!$B$1:$B$9731=G$19),(--Demand1!$D$1:$D$9731=$B$17),(Demand1!$C$1:$C$9731) ) In the sheet with the formula, I have part numbers down column A, dates across rows (19 in this case), and another text qualifier in B17. In sheet "Demand1" I have 4 columns - A has part numbers, B has dates, C has quantity (the value I want returned), and D has the text qualifier. My problem is that the formula sometimes works and sometimes doesn't - it is very tempermental. I've tried matching all the formating, and still sometimes it does not work. Anyone have any ideas? Thanks, |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple Lookup Criteria (revisited)
Use the MONTH and YEAR functions, or use something like
TEXT(cell_ref,"yyyymm") -- David Biddulph "Cam1234" wrote in message ... I kind of figured it out, but still need some help. The problem was that the dates are both formatted mmm-yy (eg, Nov-08), but the dates on the first sheet are all for the first of the month, and the dates on the second sheet are dates all over the month. Is there anyway to match it just by month and year and ignoring the day of the month? Thanks, "Cam1234" wrote: Hi All, I'm using the following formula to return data in a sheet: =SUMPRODUCT(--(Demand1!$A$1:$A$9731=$A21),(--Demand1!$B$1:$B$9731=G$19),(--Demand1!$D$1:$D$9731=$B$17),(Demand1!$C$1:$C$9731) ) In the sheet with the formula, I have part numbers down column A, dates across rows (19 in this case), and another text qualifier in B17. In sheet "Demand1" I have 4 columns - A has part numbers, B has dates, C has quantity (the value I want returned), and D has the text qualifier. My problem is that the formula sometimes works and sometimes doesn't - it is very tempermental. I've tried matching all the formating, and still sometimes it does not work. Anyone have any ideas? Thanks, |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple Lookup Criteria (revisited)
All three criteria have to be met for it to do this though, right?
"Gary''s Student" wrote: There is a slight danger in your approach. You are setting up criteria on cols A,B,D and pulling data from col C. If more than one row matches the criteria, you will get the sum of the passing col C values rather than just a single col C value. -- Gary''s Student - gsnu200814 "Cam1234" wrote: Hi All, I'm using the following formula to return data in a sheet: =SUMPRODUCT(--(Demand1!$A$1:$A$9731=$A21),(--Demand1!$B$1:$B$9731=G$19),(--Demand1!$D$1:$D$9731=$B$17),(Demand1!$C$1:$C$9731) ) In the sheet with the formula, I have part numbers down column A, dates across rows (19 in this case), and another text qualifier in B17. In sheet "Demand1" I have 4 columns - A has part numbers, B has dates, C has quantity (the value I want returned), and D has the text qualifier. My problem is that the formula sometimes works and sometimes doesn't - it is very tempermental. I've tried matching all the formating, and still sometimes it does not work. Anyone have any ideas? Thanks, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
GetPivotData with variable criteria input? (revisited!) | Excel Worksheet Functions | |||
lookup multiple criteria | Excel Worksheet Functions | |||
Lookup using multiple sheets and multiple criteria, sorry if 2 pos | Excel Worksheet Functions | |||
Lookup using multiple sheets and multiple criteria | Excel Discussion (Misc queries) | |||
Lookup with Multiple Criteria | Excel Discussion (Misc queries) |