Solved

# Subtotal

Posted on 2014-07-29
82 Views
In the attached ss i have a tab called "Sales"

Column E is a weekly subtotal columns where i have manually calculated the subtotal for two weeks, can i have some formula that can drag down and calculate the subtotals

I basically need a running total for Sundays

Thanks
Howl-Sales.xlsx
0
Question by:Seamus2626
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 5
• 4

LVL 51

Expert Comment

ID: 40226756
Hi,

pls try

as Array formula (Ctrl-Shift-Enter)

=SUMPRODUCT((\$C\$2:\$C\$1500),--(CEILING((\$A\$2:\$A\$1500-DATE(YEAR(\$A\$2:\$A\$1500),1,1)+WEEKDAY(DATE(YEAR(\$A\$2:\$A\$1500),1,1),2))/7,1)=WEEKNUM(F8,2)))+SUMPRODUCT((\$D\$2:\$D\$1500),--(CEILING((\$A\$2:\$A\$1500-DATE(YEAR(\$A\$2:\$A\$1500),1,1)+WEEKDAY(DATE(YEAR(\$A\$2:\$A\$1500),1,1),2))/7,1)=WEEKNUM(F8,2)))

Regards
0

Author Comment

ID: 40226788
Thanks Rgonzo, i enetered that in and its entering the subtotals on mondays, and has value errors and £0, where as the cell should be blank unless the corresponding cell in Col B = Sunday

Did you enter that on my ss i attached:?

Thanks
0

LVL 51

Expert Comment

ID: 40226971
Yes

see example
Howl-Salesv1.xlsx
0

Author Comment

ID: 40226999
Thing is Rgonzo, you have put that into the cells where Sunday is adjacent, whereas i need to put formula into E2 and drop down, so it is automatically finding sunday and returning a subtotal for previous week or remaining blank if not Sunday

Thanks
0

LVL 51

Expert Comment

ID: 40227064
Do you want to the last total

=IF(MAX(F3:F1500)=0,"",SUM(--(\$F\$3:\$F\$1500=MAX(F3:F1500))*IFERROR((E3:E1500)*1,0)))

in Sheet Sales 2 formula without helper's formula

BTW

Corrected Formula
=IFERROR(SUMPRODUCT((\$C\$2:\$C\$1500),--(CEILING((\$A\$2:\$A\$1500-DATE(YEAR(\$A\$2:\$A\$1500),1,1)+WEEKDAY(DATE(YEAR(\$A\$2:\$A\$1500),1,1),2))/7,1)=WEEKNUM(F8,2)))+SUMPRODUCT((\$D\$2:\$D\$1500),--(CEILING((\$A\$2:\$A\$1500-DATE(YEAR(\$A\$2:\$A\$1500),1,1)+WEEKDAY(DATE(YEAR(\$A\$2:\$A\$1500),1,1),2))/7,1)=WEEKNUM(F8,2))),"")
Howl-Salesv2.xlsx
0

Author Comment

ID: 40228569
Hi Rgonzo,

I want to put a formula in E2 and drag all the way down. When the formula sees that its corresponding value in B = Sunday, it needs to create a subtotal for the previous 7 days sales summed.

So with the data i have provided, it would look like my attached ss in tab sales

But i need to be able to have the formula already in the ss so it doesnt need to be manaully added
0

Author Comment

ID: 40228570
attached
Howl-Sales.xlsx
0

LVL 51

Accepted Solution

Rgonzo1971 earned 500 total points
ID: 40228607
Hi,

pls try in E2 and fill down

=IF(B2="Sunday",SUM(OFFSET(C2,-6,0,7,2)),"")

Regards
0

Author Closing Comment

ID: 40228630
Legend!

Thanks Rgonzo
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
###### Suggested Courses
Course of the Month7 days, 13 hours left to enroll