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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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
 
Upvote 0
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 ?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

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
Back
Top