ExcelBanter - SUMPRODUCT, INDIRECT, dates...

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - SUMPRODUCT, INDIRECT, dates... (https://www.excelbanter.com/excel-worksheet-functions/172950-sumproduct-indirect-dates.html)

LongTermNoob

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.

T. Valko

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.

LongTermNoob

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.

T. Valko

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.

LongTermNoob

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.

T. Valko

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.

All times are GMT +1. The time now is 09:53 PM.