# 2 part question on array formula

#### lazor99

##### Board Regular
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

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### Oaktree

##### MrExcel MVP
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

##### Legend
lazor99 said:
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

##### Board Regular
Oaktree

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

#### just_jon

##### Legend
lazor99 said:
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)

Replies
5
Views
253
Replies
3
Views
256
Replies
1
Views
176
Replies
6
Views
453
Replies
2
Views
204

1,195,749
Messages
6,011,437
Members
441,614
Latest member
TiaGtz

### 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.

### Which adblocker are you using?

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

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