SUM and IF using two date ranges


T

TomR

Hi there: 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.
 
Ad

Advertisements

D

Don Guillett

Assuming real dates, simplify for the month desired

=SUMproduct((month(A1:A10)=1)*B1:B10)
 
Ad

Advertisements

T

TomR

This worked perfectly...Thanks, Don :)
Don Guillett said:
Assuming real dates, simplify for the month desired

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


.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top