About Us

TomR

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.

.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How do I get a new calculated date from different date ranges?	Ann LeBlanc	Excel Worksheet Functions	3	June 26th 09 01:56 PM
Date Ranges	Liz J[_2_]	Excel Discussion (Misc queries)	2	January 16th 09 03:54 AM
date ranges	Carolina Girl	Excel Worksheet Functions	3	May 9th 08 10:07 PM
Date ranges	Fortune	Excel Worksheet Functions	1	March 28th 07 05:18 PM
compare date to various date ranges and sum value	al	Excel Worksheet Functions	2	January 10th 07 10:17 AM

Menu

About Us