Sumproduct

J

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

jaybird2307

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top