# A SUMPRODUCT() Problem or is it

#### Peter100

##### Well-known Member
I have a sheet that shows the weekly sales (Columns A B & C)

By entering the criteria in E3 and E4 it will select and display the individual weekly total in G3. If you enter "ALL" in F3 it will display the sum of all values in coumn C that correspond to the Year in E3.

This enables me to now show individual weeks or Calendar year totals

MY PROBLEM
I want to display Financial year totals that can cross from one year to the next (in reality this is Week >31 in year 2001 to week 31 in 2002)

I have given names to the corresponding ranges in Column A ie year2001, 2002. But I cant seem to make it happen

Anyone any ideas ?????
Sales.xls
ABCDEFG
1DATASELECTION
2YearWeekSalesYearWeekSales
3200151100002001all21000
420015211000
5200218000
6200229000
7
8Thisisanextractfromamuchlargersheet
9
Sheet1

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

##### MrExcel MVP
Peter,

Make

=IF(F3="All",SUMPRODUCT((\$A\$3:\$A\$6=E3)*\$C\$3:\$C\$6),SUMPRODUCT((\$A\$3:\$A\$6=E3)*(\$B\$3:\$B\$6=F3)*\$C\$3:\$C\$6))

less expensive:

=IF(F3="All",SUMIF(\$A\$3:\$A\$6,E3,\$C\$3:\$C\$6),SUMPRODUCT((\$A\$3:\$A\$6=E3)*(\$B\$3:\$B\$6=F3)*\$C\$3:\$C\$6))

If your source data has a column showing weeknumbers (using WEEKNUM), you'll be able to set up a SUMPRODUCT formula to solve your problem.

EDITED for an extraneous space within SUMIF.
This message was edited by Aladin Akyurek on 2002-10-25 16:14

#### Peter100

##### Well-known Member

Yes I agree your option is less expensive.

But how would I use WEEKNUM when it includes not true dates ie. it is a text entry of simply 25 26 27 etc, and how would it cross fromm year to year whereby I want weeks 32-52 from year 2001 + weeks 1-31 in 2002 ?

#### Peter100

##### Well-known Member

Just tried your "Less Expensive" formula and good old Excell wants to add 2 more closing parenthesis but it then errors with #VALUE

#### Barrie Davidson

##### MrExcel MVP
What about inserting another column that indicates financial year using a formula like:
=IF(B3<32,A3,A3-1)

Would this idea work for you?

#### Peter100

##### Well-known Member
Hi Barrie

Yes it would but I was hoping not to have to insert another column as I felt it must be able to be handled formula wise.

#### Barrie Davidson

##### MrExcel MVP

=IF(F3="All",SUMPRODUCT((\$A\$3:\$A\$6=E3)*(\$B\$3:\$B\$6>31)*\$C\$3:\$C\$6)+SUMPRODUCT((\$A\$3:\$A\$6=E3+1)*(\$B\$3:\$B\$6<32)*\$C\$3:\$C\$6),SUMPRODUCT((\$A\$3:\$A\$6=E3)*(\$B\$3:\$B\$6=F3)*\$C\$3:\$C\$6))

Any help?

#### Peter100

##### Well-known Member
Hi Barrie

Wel Done its cracked.

Sorry it took a while to get back

##### MrExcel MVP
On 2002-10-25 10:08, Peter100 wrote:

Just tried your "Less Expensive" formula and good old Excell wants to add 2 more closing parenthesis but it then errors with #VALUE

It should be:

=IF(F3="All",SUMIF(\$A\$3:\$A\$6,E3,\$C\$3:\$C\$6),SUMPRODUCT((\$A\$3:\$A\$6=E3)*(\$B\$3:\$B\$6=F3)*\$C\$3:\$C\$6))

There was an extraneous space within SUMIF.

##### MrExcel MVP
On 2002-10-25 16:03, Peter100 wrote:
Hi Barrie

Wel Done its cracked.

Sorry it took a while to get back

Great.

A side note. I didn't take your "all" as meaning the fiscal year sum. That's why I left the latter to you.

Replies
2
Views
230
Replies
6
Views
318
Replies
9
Views
1K
Replies
0
Views
107
Replies
2
Views
1K

1,181,539
Messages
5,930,490
Members
436,741
Latest member
buckeyerich

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back