SUM and IF using two date ranges
 

Hi the I have a spreadsheet that I am modifying. Currently it tracks all
the deposits my business makes by deposit date and amount. It also tracks
the sales tax I need to pay on a quarterly basis. I ran into this problem
trying to use SUMIF when I first created the spreadsheet and my solution at
the time was to simply create 4 sections; each representing a quarter and
allowing for 13 deposits (on a week) plus an extra just in case.

Now the government wants their sales tax monthly in 2010 so I want to find a
way to evaluate each deposit date and total by month. Additionally, things
have gotten busier so I often do more than one deposit a week.

Microsoft support had a write-up showing a formula fur using SUM and IF:

=SUM(IF((A1:A10=DATEVALUE("1/10/99"))*(A1:A10<=DATEVALUE("2/10/99")),B1:B10,0))

....which I modified as follows:

=SUM(IF((B4:B61=DATEVALUE("01/01"))*(B4:B61<DATEVALUE("02/01")),H4:H61,0))

€¦where column B represents the Deposit Date and Column H represents the
Sales Tax to be paid for that deposit. This example is for the month of
January. Similar formulas would exist for each of the other months as well.

The problem is that the Formula returns a #VALUE error even though when I
interrogate the function the formula seems to be returning the proper answer.
I have tried to simplify the formula just to test pieces but to no avail.
The format of the Deposit Date column is mm/dd/yy. I would like to write
this formula so it transcends year, just in case the Gov leaves me alone and
I can carry the worksheet into future years. Advice? Thanks in advance.

SUM and IF using two date ranges
 

Assuming real dates, simplify for the month desired

=SUMproduct((month(A1:A10)=1)*B1:B10)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"TomR" wrote in message
...
Hi the I have a spreadsheet that I am modifying. Currently it tracks
all
the deposits my business makes by deposit date and amount. It also tracks
the sales tax I need to pay on a quarterly basis. I ran into this problem
trying to use SUMIF when I first created the spreadsheet and my solution
at
the time was to simply create 4 sections; each representing a quarter and
allowing for 13 deposits (on a week) plus an extra just in case.

Now the government wants their sales tax monthly in 2010 so I want to find
a
way to evaluate each deposit date and total by month. Additionally,
things
have gotten busier so I often do more than one deposit a week.

Microsoft support had a write-up showing a formula fur using SUM and IF:

=SUM(IF((A1:A10=DATEVALUE("1/10/99"))*(A1:A10<=DATEVALUE("2/10/99")),B1:B10,0))

...which I modified as follows:

=SUM(IF((B4:B61=DATEVALUE("01/01"))*(B4:B61<DATEVALUE("02/01")),H4:H61,0))

€¦where column B represents the Deposit Date and Column H represents the
Sales Tax to be paid for that deposit. This example is for the month of
January. Similar formulas would exist for each of the other months as
well.

The problem is that the Formula returns a #VALUE error even though when I
interrogate the function the formula seems to be returning the proper
answer.
I have tried to simplify the formula just to test pieces but to no avail.
The format of the Deposit Date column is mm/dd/yy. I would like to write
this formula so it transcends year, just in case the Gov leaves me alone
and
I can carry the worksheet into future years. Advice? Thanks in advance.

SUM and IF using two date ranges
 

This worked perfectly...Thanks, Don :)
"Don Guillett" wrote:

Assuming real dates, simplify for the month desired

=SUMproduct((month(A1:A10)=1)*B1:B10)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"TomR" wrote in message
...
Hi the I have a spreadsheet that I am modifying. Currently it tracks
all
the deposits my business makes by deposit date and amount. It also tracks
the sales tax I need to pay on a quarterly basis. I ran into this problem
trying to use SUMIF when I first created the spreadsheet and my solution
at
the time was to simply create 4 sections; each representing a quarter and
allowing for 13 deposits (on a week) plus an extra just in case.

Now the government wants their sales tax monthly in 2010 so I want to find
a
way to evaluate each deposit date and total by month. Additionally,
things
have gotten busier so I often do more than one deposit a week.

Microsoft support had a write-up showing a formula fur using SUM and IF:

=SUM(IF((A1:A10=DATEVALUE("1/10/99"))*(A1:A10<=DATEVALUE("2/10/99")),B1:B10,0))

...which I modified as follows:

=SUM(IF((B4:B61=DATEVALUE("01/01"))*(B4:B61<DATEVALUE("02/01")),H4:H61,0))

€¦where column B represents the Deposit Date and Column H represents the
Sales Tax to be paid for that deposit. This example is for the month of
January. Similar formulas would exist for each of the other months as
well.

The problem is that the Formula returns a #VALUE error even though when I
interrogate the function the formula seems to be returning the proper
answer.
I have tried to simplify the formula just to test pieces but to no avail.
The format of the Deposit Date column is mm/dd/yy. I would like to write
this formula so it transcends year, just in case the Gov leaves me alone
and
I can carry the worksheet into future years. Advice? Thanks in advance.

.