2 part question on array formula

[lazor99]

This formula would sum current monthly total
=sum(if(MONTH(A1:A365)=MONTH(TODAY()),B1:B365,0))

What change would i have to make in the formula to sum "past 7 days" of current monthly total.

Also if a zero dollar ($0.00) amount entry is in the sum of the past 7 day total, how would you skip that entry and add only amounts greater than $0.01 for the past 7 days.

Thanks, Lazor99

 

[Oaktree]

Please be more specific to "past 7 days" of current monthly total:

What happens if today is November 5 so there have only been 4 (5 counting today) days in November? Do you want to include 3 (2) days from October?

Do you want all days? Or working days?

 

[just_jon]

This formula would sum current monthly total
=sum(if(MONTH(A1:A365)=MONTH(TODAY()),B1:B365,0))

What change would i have to make in the formula to sum "past 7 days" of current monthly total.

Also if a zero dollar ($0.00) amount entry is in the sum of the past 7 day total, how would you skip that entry and add only amounts greater than $0.01 for the past 7 days.

Thanks, Lazor99

I'd use

=SUMPRODUCT(--(A1:A365>TODAY()-7),--(A1:A365<=TODAY()),--(B1:B365>.01),B1:B365)

EDITed to change >=.01 to >.01

 

[lazor99]

Oaktree

Just for the CURRENT month. No days in Oct should be sumed.

 

[just_jon]

Oaktree

Just for the CURRENT month. No days in Oct should be sumed.

In that case -

=SUMPRODUCT(--(MONTH(A1:A365)=MONTH(TODAY())),--(A1:A365>TODAY()-7),--(A1:A365<=TODAY()),--(B1:B365>0.01),B1:B365)