A SUMPRODUCT() Problem or is it

Peter100

Well-known Member
Joined
Apr 21, 2002
Messages
765
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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.

Aladin

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

Peter100

Well-known Member
Joined
Apr 21, 2002
Messages
765
Hi Aladin

I'm pleased YOU responded.

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
Joined
Apr 21, 2002
Messages
765
Hi Aladin

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
Joined
Feb 10, 2002
Messages
2,330

ADVERTISEMENT

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
Joined
Apr 21, 2002
Messages
765
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
Joined
Feb 10, 2002
Messages
2,330

ADVERTISEMENT

What about this formula (assumes week number 31 or less belongs to prior fiscal year):

=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?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
On 2002-10-25 10:08, Peter100 wrote:
Hi Aladin

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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.

Aladin
 

Forum statistics

Threads
1,143,915
Messages
5,721,520
Members
422,369
Latest member
redinator

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top