# SUM IF ??

#### glerwell

##### Well-known Member
Hi

I want to sum all amounts of money in a column for just today and yesterday. I don't want to create a pivot table, so I was wondering whether there is another way around this.

Could the SUMIF and VTODAY functions be combined to create a formula?

The dates are in column B and the amounts in column R.

Thanks

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### ageren

##### Board Regular
insert below formula into cell S1 and copy it at the end of your range;

=IF(OR(B1=TODAY();B1=TODAY()-1);R1;0)

for the result, insert below formula into cell T1;

= sum("S1:S65536")

#### glerwell

##### Well-known Member
i can't enter the following formula, and error message comes up saying "the formula you typed contains an error"

=IF(OR(B1=TODAY();B1=TODAY()-1);R1;0)

#### ageren

##### Board Regular
pls try like that:

=IF(OR(B1=TODAY(),B1=TODAY()-1),R1,0)

#### glerwell

##### Well-known Member
works fine thanks

#### ExcelChampion

##### Well-known Member
For one all encompassing formula that you enter only in to one cell:

=SUMIF(B:B,">="&TODAY()-1,R:R)

#### ageren

##### Board Regular
if the column B contains the date after today, your result would be wrong... if you develop your formula contains that condition, i think the result would be fine..

#### ExcelChampion

##### Well-known Member
I assumed his data only included up to today.

In that case:

Code:
``=SUMPRODUCT(--(B1:B10>=TODAY()-1),--(B1:B10<TODAY()+1),R1:R10)``

Or, for a dynamic range approach:

Code:
``=SUMPRODUCT(--(B2:INDEX(B:B,MATCH(9.99999999999999E+307,B:B))>=TODAY()-1),--(B2:INDEX(B:B,MATCH(9.99999999999999E+307,B:B))<TODAY()+1),R2:INDEX(R:R,MATCH(9.99999999999999E+307,B:B)))``

##### MrExcel MVP
If there is never any date beyond today's date in column B...

=SUMIF(B:B,">="&TODAY()-1,R:R)

Replies
4
Views
232
Replies
2
Views
377
Replies
1
Views
489
Replies
1
Views
355
Replies
5
Views
319

1,191,170
Messages
5,985,061
Members
439,937
Latest member
MAlhash

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