Remember Me?

#1
November 9th 05, 04:07 PM
 markx Posts: n/a
include INDIRECT function into SUMPRODUCT formula

Hi guys,

I have the following formula:
=SUMPRODUCT((Archived!\$O1:\$O5135=\$A2)*(Archived!\$H 1:\$H5135=D\$1))

However, the range (in this exemple going from row 1 to row 5135) changes
every month, so, if my understanding is correct, I can either:
- change it manually
- use "dynamically named ranges" or
- use "indirect" function that will refer to the =COUNT(Archived!O:O) that I
will put somewhere.

So, my question is: do you know how to include INDIRECT statement into the
above-mentionned SUMPRODUCT function?

Mark

#2
November 9th 05, 05:04 PM
 Gary Posts: n/a
include INDIRECT function into SUMPRODUCT formula

Somthing like:

=SUMPRODUCT((INDIRECT("Archived!\$O1:\$O"&COUNT(Arch ived!O:O))=\$A2)*(INDIRECT("Archived!\$H1:\$H"&COUNT( Archived!H:H))=D\$1))

HTH

markx wrote:
Hi guys,

I have the following formula:
=SUMPRODUCT((Archived!\$O1:\$O5135=\$A2)*(Archived!\$H 1:\$H5135=D\$1))

However, the range (in this exemple going from row 1 to row 5135) changes
every month, so, if my understanding is correct, I can either:
- change it manually
- use "dynamically named ranges" or
- use "indirect" function that will refer to the =COUNT(Archived!O:O) that I
will put somewhere.

So, my question is: do you know how to include INDIRECT statement into the
above-mentionned SUMPRODUCT function?

Mark

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post neil Excel Discussion (Misc queries) 1 August 11th 05 09:19 AM sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 07:48 PM sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 03:28 AM ShaneS Excel Worksheet Functions 1 May 17th 05 03:24 AM [email protected] Excel Worksheet Functions 19 May 11th 05 09:48 AM

All times are GMT +1. The time now is 02:55 PM.