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.
|