Sumproduct issues.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lewiselw - 25 Mar 2008 15:54 GMT

I am trying to add an arai of figures together based on customer name and week.
The sheet that I am collecting the data from has a list of invoices and the
week when they are due, being pulled in Via SQL query (in case that makes a
difference)
The client is using Excel 2000,

Simply put
Customer, Inv_Amount, Week_No

I want to display as:
Week 1 Week 2 Week 3
Customer xxxxx xxxxx xxxxxx

The formula that I have used is as follows.

=SUMPRODUCT('Sales Actuals'!$F$2:$F$323,('Sales Actuals'!$B$2:$B$323='Filled
Sheets'!$B7)+0,('Sales Actuals'!$L$2:$L$323='Filled Sheets'!L$5)+0)

but it does not work and returns a #NA value on all cells.

Reply to this Message

PCLIVE - 25 Mar 2008 16:12 GMT

I'm not sure exactly what this formula is supposed to accomplish. Are you
trying to sum the values in F2:F323 where the adjacent cells in columns B
and L match the criteria in Filled Sheets B7 and L5? If so, then maybe
this:

=SUMPRODUCT(--('Sales Actuals'!$B$2:$B$323='Filled Sheets'!$B7),--('Sales
Actuals'!$L$2:$L$323='Filled Sheets'!L$5),'Sales Actuals'!$F$2:$F$323)

HTH,
Paul

>I am trying to add an arai of figures together based on customer name and
>week.
[quoted text clipped - 19 lines]
>
> but it does not work and returns a #NA value on all cells.

Reply to this Message