| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT, INDIRECT, dates...
Hi,
I recently asked for help looking up values in one column by the year in another column on different worksheets, and was very kindly given the following solution: =SUMPRODUCT(--(YEAR(INDIRECT("'"&A3&"'!H8:H1000"))=2008),INDIREC T("'"&A3&"'!I8:I1000")) I have now been asked to add sub-totals by month! I came up with: =SUMPRODUCT(AND(--(YEAR(INDIRECT("'"&A3&"'!H8:H1000"))=2008),(--(MONTH(INDIRECT("'"&A3&"'!H8:H1000"))=1))),INDIREC T("'"&A3&"'!I8:I1000")) however this returns a #VALUE! error. I have also tried JANUARY instead of 1 for the month criteria - same result. If anyone could help me on this I would be very grateful. TIA. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT, INDIRECT, dates...
Try something like this:
=SUMPRODUCT(--(TEXT(INDIRECT("'"&A3&"'!H8:H1000"),"yyyy/m")="2008/1"),INDIRECT("'"&A3&"'!I8:I1000")) -- Biff Microsoft Excel MVP "LongTermNoob" wrote in message ... Hi, I recently asked for help looking up values in one column by the year in another column on different worksheets, and was very kindly given the following solution: =SUMPRODUCT(--(YEAR(INDIRECT("'"&A3&"'!H8:H1000"))=2008),INDIREC T("'"&A3&"'!I8:I1000")) I have now been asked to add sub-totals by month! I came up with: =SUMPRODUCT(AND(--(YEAR(INDIRECT("'"&A3&"'!H8:H1000"))=2008),(--(MONTH(INDIRECT("'"&A3&"'!H8:H1000"))=1))),INDIREC T("'"&A3&"'!I8:I1000")) however this returns a #VALUE! error. I have also tried JANUARY instead of 1 for the month criteria - same result. If anyone could help me on this I would be very grateful. TIA. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT, INDIRECT, dates...
Thanks, Biff, this works a treat!
Out of curiosity, is there any reason I couldn't do it with the AND statement? Or does it just make things too complicated? Thanks again, Roger "T. Valko" wrote: Try something like this: =SUMPRODUCT(--(TEXT(INDIRECT("'"&A3&"'!H8:H1000"),"yyyy/m")="2008/1"),INDIRECT("'"&A3&"'!I8:I1000")) -- Biff Microsoft Excel MVP "LongTermNoob" wrote in message ... Hi, I recently asked for help looking up values in one column by the year in another column on different worksheets, and was very kindly given the following solution: =SUMPRODUCT(--(YEAR(INDIRECT("'"&A3&"'!H8:H1000"))=2008),INDIREC T("'"&A3&"'!I8:I1000")) I have now been asked to add sub-totals by month! I came up with: =SUMPRODUCT(AND(--(YEAR(INDIRECT("'"&A3&"'!H8:H1000"))=2008),(--(MONTH(INDIRECT("'"&A3&"'!H8:H1000"))=1))),INDIREC T("'"&A3&"'!I8:I1000")) however this returns a #VALUE! error. I have also tried JANUARY instead of 1 for the month criteria - same result. If anyone could help me on this I would be very grateful. TIA. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT, INDIRECT, dates...
You could also do it this way:
=SUMPRODUCT(--(YEAR(INDIRECT("'"&A3&"'!H8:H1000"))=2008),--(MONTH(INDIRECT("'"&A3&"'!H8:H1000"))=1),INDIRECT( "'"&A3&"'!I8:I1000") The formula I suggested is a bit shorter. -- Biff Microsoft Excel MVP "LongTermNoob" wrote in message ... Thanks, Biff, this works a treat! Out of curiosity, is there any reason I couldn't do it with the AND statement? Or does it just make things too complicated? Thanks again, Roger "T. Valko" wrote: Try something like this: =SUMPRODUCT(--(TEXT(INDIRECT("'"&A3&"'!H8:H1000"),"yyyy/m")="2008/1"),INDIRECT("'"&A3&"'!I8:I1000")) -- Biff Microsoft Excel MVP "LongTermNoob" wrote in message ... Hi, I recently asked for help looking up values in one column by the year in another column on different worksheets, and was very kindly given the following solution: =SUMPRODUCT(--(YEAR(INDIRECT("'"&A3&"'!H8:H1000"))=2008),INDIREC T("'"&A3&"'!I8:I1000")) I have now been asked to add sub-totals by month! I came up with: =SUMPRODUCT(AND(--(YEAR(INDIRECT("'"&A3&"'!H8:H1000"))=2008),(--(MONTH(INDIRECT("'"&A3&"'!H8:H1000"))=1))),INDIREC T("'"&A3&"'!I8:I1000")) however this returns a #VALUE! error. I have also tried JANUARY instead of 1 for the month criteria - same result. If anyone could help me on this I would be very grateful. TIA. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT, INDIRECT, dates...
Excellent. Many thanks again...
Roger "T. Valko" wrote: You could also do it this way: =SUMPRODUCT(--(YEAR(INDIRECT("'"&A3&"'!H8:H1000"))=2008),--(MONTH(INDIRECT("'"&A3&"'!H8:H1000"))=1),INDIRECT( "'"&A3&"'!I8:I1000") The formula I suggested is a bit shorter. -- Biff Microsoft Excel MVP "LongTermNoob" wrote in message ... Thanks, Biff, this works a treat! Out of curiosity, is there any reason I couldn't do it with the AND statement? Or does it just make things too complicated? Thanks again, Roger "T. Valko" wrote: Try something like this: =SUMPRODUCT(--(TEXT(INDIRECT("'"&A3&"'!H8:H1000"),"yyyy/m")="2008/1"),INDIRECT("'"&A3&"'!I8:I1000")) -- Biff Microsoft Excel MVP "LongTermNoob" wrote in message ... Hi, I recently asked for help looking up values in one column by the year in another column on different worksheets, and was very kindly given the following solution: =SUMPRODUCT(--(YEAR(INDIRECT("'"&A3&"'!H8:H1000"))=2008),INDIREC T("'"&A3&"'!I8:I1000")) I have now been asked to add sub-totals by month! I came up with: =SUMPRODUCT(AND(--(YEAR(INDIRECT("'"&A3&"'!H8:H1000"))=2008),(--(MONTH(INDIRECT("'"&A3&"'!H8:H1000"))=1))),INDIREC T("'"&A3&"'!I8:I1000")) however this returns a #VALUE! error. I have also tried JANUARY instead of 1 for the month criteria - same result. If anyone could help me on this I would be very grateful. TIA. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT, INDIRECT, dates...
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "LongTermNoob" wrote in message ... Excellent. Many thanks again... Roger "T. Valko" wrote: You could also do it this way: =SUMPRODUCT(--(YEAR(INDIRECT("'"&A3&"'!H8:H1000"))=2008),--(MONTH(INDIRECT("'"&A3&"'!H8:H1000"))=1),INDIRECT( "'"&A3&"'!I8:I1000") The formula I suggested is a bit shorter. -- Biff Microsoft Excel MVP "LongTermNoob" wrote in message ... Thanks, Biff, this works a treat! Out of curiosity, is there any reason I couldn't do it with the AND statement? Or does it just make things too complicated? Thanks again, Roger "T. Valko" wrote: Try something like this: =SUMPRODUCT(--(TEXT(INDIRECT("'"&A3&"'!H8:H1000"),"yyyy/m")="2008/1"),INDIRECT("'"&A3&"'!I8:I1000")) -- Biff Microsoft Excel MVP "LongTermNoob" wrote in message ... Hi, I recently asked for help looking up values in one column by the year in another column on different worksheets, and was very kindly given the following solution: =SUMPRODUCT(--(YEAR(INDIRECT("'"&A3&"'!H8:H1000"))=2008),INDIREC T("'"&A3&"'!I8:I1000")) I have now been asked to add sub-totals by month! I came up with: =SUMPRODUCT(AND(--(YEAR(INDIRECT("'"&A3&"'!H8:H1000"))=2008),(--(MONTH(INDIRECT("'"&A3&"'!H8:H1000"))=1))),INDIREC T("'"&A3&"'!I8:I1000")) however this returns a #VALUE! error. I have also tried JANUARY instead of 1 for the month criteria - same result. If anyone could help me on this I would be very grateful. TIA. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumproduct with indirect | Excel Worksheet Functions | |||
| SUMPRODUCT & INDIRECT? | Excel Worksheet Functions | |||
| Need help with using SUMPRODUCT with INDIRECT | Excel Worksheet Functions | |||
| Help with Sumproduct with Indirect | Excel Worksheet Functions | |||
| sumproduct & indirect | Excel Worksheet Functions | |||
Linear Mode
