As I understand the SUMPRODUCT function, the ranges have to be the same
size in order for this to work.

Let me try an example to illustrate:

I have two worksheets.

On the first is a list of all customer numbers (I have 1,240
customers).

On the second is a table of sales $ (by customer number). This table
has 13 columns: Customer #, Year, Jan, Feb, Mar, Apr, etc. The second
worksheet does not contain a record for every customer number (some
customers don't have sales $ in the table), and, some customer numbers
will have more than one record (year 2004, 2005, 2006 etc.).

On the first worksheet beside each customer #, I want to sum up
information from my second sheet where the "year" value is equal to X.
If a customer did not have sales, then I want to return the value 0.

What the SUMPRODUCT formula is doing for me now is working, but only on
the rows of my first worksheet that are within the row range of my
second worksheet. (Worksheet #1 has 1240 customer numbers listed.
Worksheet #2 has only 1032 records. Worksheet #1 SUMPRODUCT formula
works up through row #1032. Formula does not work for rows 1033 through
1240.)

Hope you can help.
Thx.


--
jaybird2307
------------------------------------------------------------------------
jaybird2307's Profile: http://www.excelforum.com/member.php...o&userid=35577
View this thread: http://www.excelforum.com/showthread...hreadid=559009

What's the formula?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jaybird2307"
wrote in message
...

As I understand the SUMPRODUCT function, the ranges have to be the same
size in order for this to work.

Let me try an example to illustrate:

I have two worksheets.

On the first is a list of all customer numbers (I have 1,240
customers).

On the second is a table of sales $ (by customer number). This table
has 13 columns: Customer #, Year, Jan, Feb, Mar, Apr, etc. The second
worksheet does not contain a record for every customer number (some
customers don't have sales $ in the table), and, some customer numbers
will have more than one record (year 2004, 2005, 2006 etc.).

On the first worksheet beside each customer #, I want to sum up
information from my second sheet where the "year" value is equal to X.
If a customer did not have sales, then I want to return the value 0.

What the SUMPRODUCT formula is doing for me now is working, but only on
the rows of my first worksheet that are within the row range of my
second worksheet. (Worksheet #1 has 1240 customer numbers listed.
Worksheet #2 has only 1032 records. Worksheet #1 SUMPRODUCT formula
works up through row #1032. Formula does not work for rows 1033 through
1240.)

Hope you can help.
Thx.


--
jaybird2307
------------------------------------------------------------------------
jaybird2307's Profile:
http://www.excelforum.com/member.php...o&userid=35577
View this thread: http://www.excelforum.com/showthread...hreadid=559009

=SUMPRODUCT(--(Worksheet!$B$7=Sheet2!GenLedger.GL_Year),--(Sheet1!Select1.GL_Acct_Number=Sheet2!GenLedger.GL _Acct_Number),Sheet2!GenLedger.GL_Period_04)


--
jaybird2307
------------------------------------------------------------------------
jaybird2307's Profile: http://www.excelforum.com/member.php...o&userid=35577
View this thread: http://www.excelforum.com/showthread...hreadid=559009