Sumproduct

[jaybird2307]

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.

[Bob Phillips]

What's the formula?

--
HTH

Bob Phillips

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

"jaybird2307" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[jaybird2307]

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