SUM IF ??

[glerwell]

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

 

[ageren]

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]

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]

pls try like that:

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

 

[glerwell]

works fine thanks

 

[ExcelChampion]

For one all encompassing formula that you enter only in to one cell:

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

 

[ageren]

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]

I assumed his data only included up to today.

In that case:

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

Or, for a dynamic range approach:

=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)))

 

[Aladin Akyurek]

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

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