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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
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 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
Commented:
Yes

see example
Howl-Salesv1.xlsx
0
Author 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
Commented:
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 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
Author Commented:
attached
Howl-Sales.xlsx
0
Commented:
Hi,

pls try in E2 and fill down

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

Regards
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Legend!

Thanks Rgonzo
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.