Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUM and IF using two date ranges
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get a new calculated date from different date ranges? | Excel Worksheet Functions | |||
Date Ranges | Excel Discussion (Misc queries) | |||
date ranges | Excel Worksheet Functions | |||
Date ranges | Excel Worksheet Functions | |||
compare date to various date ranges and sum value | Excel Worksheet Functions |