Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 87
  • Last Modified:

Subtotal

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
Seamus2626
Asked:
Seamus2626
  • 5
  • 4
1 Solution
 
Rgonzo1971Commented:
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
 
Seamus2626Author Commented:
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
 
Rgonzo1971Commented:
Yes

see example
Howl-Salesv1.xlsx
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Seamus2626Author Commented:
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
 
Rgonzo1971Commented:
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
 
Seamus2626Author Commented:
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
 
Seamus2626Author Commented:
attached
Howl-Sales.xlsx
0
 
Rgonzo1971Commented:
Hi,

pls try in E2 and fill down

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

Regards
0
 
Seamus2626Author Commented:
Legend!

Thanks Rgonzo
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now