SUMIF Between Two Dates
You probaby have used SUMIF to sum a column of numbers that meet a certain condition, but what if you want to sum only those entries that are between two numbers? For that, you will need two SUMIF functions: the sum of the entries below the lower number subtracted from the sum of the entries below the higher number. An example, you say? Okay. Assume you have a list of dates and corresponding amounts and you want to sum the amounts for a particular week, say 3/29/04 through 4/2/04. Starting in A1, the list looks like this
| 3/28/04 |
100
|
| 3/29/04 |
200
|
| 3/30/04 |
300
|
| 3/31/04 |
400
|
| 4/1/04 |
500
|
| 4/2/04 |
600
|
| 4/3/04 |
700
|
| 4/5/04 |
800
|
The formula to accomplish this is:
=SUMIF(A1:A9,"<="&DATE(2004,4,2),B1:B9)-SUMIF(A1:A9,"<"&DATE(2004,3,29),B1:B9)
And, of course, this will work for any numbers, not just dates.
Congratulations on your new blog. It’s about time that somebody started writing a blog about Excel! I love Excel and have about 25 books on Excel. Today I just received “Spreadsheet Modeling & Decision Analysis” by Cliff Ragsdale. This is a fantastic book. I also have most of John Walkenbach’s Excel books. I very much enjoy John’s Excel Charts book. I’d like to see Microsoft improve Excel’s charting capabilities. Again, congrats - I very much look forward to reading your blog!
This is just what I was looking for; an exact solution for the exact problem I have.
The one challenge I am having is this does not work for me.
In the example, the indicated amounts should total: 2000, but when I tried using the formula, excel says: 6/22/1905.
I am using version 2003, is this my problem?
Eric: The problem is that sometimes Excel thinks it knows what you want. In this case, it thinks you want a date and it formats the cell as a date. If you format the cell as a number, it will look right.
Thanks a million - saved me loads of head-scratching!
It may be worth noting on this page that usefullness of the COUNTIF function. If you wish to count the number of dates between x & y instead of summing a different columns values, simply replacing SUMIF with COUNTIF and removing the 3rd argument of SUMIF will provide the results you require. eg:
=SUMIF(A1:A9,”<=”&DATE(2004,4,2))-SUMIF(A1:A9,”<”&DATE(2004,3,29))
Haydn
The SUMIF and COUNTIF functions can also be combined here to calculate an average (by dividing the sum by the count). This was very useful in some work I was just doing.
This is just what I was looking for, with a slight variation… I wanted to use todays date as the condition, i.e.
=SUMIF(E2:BR2,”<=”&TODAY(),E14:BR14)
Thanks!
Very nice. I have been working around this issue for yonks. Can’t believe it was that simple!
Thanks very much for the posting. I was looking for some sort of between function like you would see in crystal reports. This worked very well.
This tip has been amazingly useful. Now, I’d like to add a small wrinkle. What if the data to be summed contains both negative and positive numbers, and I only want the positive numbers associated with the specified date range to be included in the sum? I can’t figure out where to add this additional clause. Please help me out if you’ve got a minute. Thank you!
Hi
use sUMPRODUCT. See:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
This formula works great, except I am trying to lookup a range of dates on numerous columns, not just one like the example given above. I am going to paste the formula I am attemtping, please help.
The dates are in the worksheet traffic database A10:A550 and the results of what I want to get are in cells B10:U549 on the traffic database worksheet. I tried this formula below, but for some reason it is only getting the results from B1O:B549
please help so I can get all the results…thank you
=SUMIF(TrafficDataBase!A10:A550,”
=SUMIF(TrafficDataBase!A10:A550,”
Can’t thank you enough for this answer and for this site!
I’m not sure if this will help, but I have to track billings and collections via spreadsheet and wanted to be able to look at my aging for any given 30 day period.
Columns are:
A
I’m not sure if anyone will find this useful, but I have to track billings and
collections via spreadsheet and wanted to be able to look at my aging for
any given 30 day period.
Columns are:
A: Customer #
B: Customer Name
C: Invoice Date
D: Invoice Number
E: Invoice Amount (total)
F: Amount Paid (against Invoice)
G: Payment Date (really deposit date)
My Summary section is a couple of rows past the end of the table, and I enter
the starting day of the month (usually the 1st) in cell: $F$48.
As long as I am working in the month I want my 30 day window periods to
slide according to the current date. Once the month has ended, the window
has to stop on or around the last day of the month. So I have an If statement
embedded to manage that:
IF(TODAY()-$F$48
Hi there
Thank you in advance for your consideration of the following. I am picking up on the discussion about SUMIF between two dates where the following was provided:
3/28/04 100
3/29/04 200
3/30/04 300
3/31/04 400
4/1/04 500
4/2/04 600
4/3/04 700
4/5/04 800
The formula to accomplish this is:
=SUMIF(A1:A9,”
Is there a way to make this work using horizontally organzied data? For example, to sum a year-to-date row of data on a financial statement, where each column represents a different month?
Would use the formula with a date reference which would allow just changing the reference instead of changing the formula on each financial statement line.
Jay,
Sure can!
=SUMIF(A1:L1, “
That comment parser sure is a pain… trying again:
Jay,
Sure can!
=SUMIF(A1:L1, "<=" & B13, A5:L5) - SUMIF(A1:L1, "<" & B12, A5:L5)
Where:
A1:L1 are months eg. 1-Jan 1-Feb 1-Mar … 1-Dec
A5:L5 are subtotals
B12 is Month From
B13 is Month To
Rob
OK, this is ALMOST what I need…
I need to count the occurences of a given value, let’s say in column A that occur between two dates in column B….
I’ve been banging my headagainst this one and hopefully you can help me out.
Can you do sumif with 2 criteria?
For example, if I had a 3rd column that had some string value like “A” and I wanted all “A” values falling between 2 dates … so, i guess i’m looking for nested sumifs?
Lance,
Check out this example which uses SUMPRODUCT.
Multicolumn Sum with Wildcard
Cheers,
Rob
Thank you for your site. I have been wanting this answer [SUMIF between 2 dates] which will be very useful where I work in Sales. Your website was straightforward and most appreciated.
I read through all the posts but didn’t quite find the formula that I need. I want to sum an “Amount Paid” (A:A) column when the “Date Paid” (B:B) column falls between two dates that I will enter in two cells and not in the formula itself. C1= “From Date” and D1= “To Date”.
Example in plain English, I think: If Date Paid = C1 thru D1 then SUM A:A.
The formula that I’m currently experimenting with looks like this:
=SUMIF(B:B,C1,A:A) This will only work for one date and not a range of dates as I require.
Thank you,
Chris
Just another note. I re-read post #20 I think this is very close to what I need. I will play with it while awaiting a reply.
Thank you again,
Chris
Well it didn’t take any playing with at all. Just changed the cell ranges and refs to my own and like magic it worked! This blog saved me endless hours of frustration. Thank you to Dicks Blog and thank you to Rob Van Gelder for his post. I’m soooo happy that I’m giddy!
Chris
Hi,
Firstly, this is a fantastic site and very easy to use.
the above is nearly what i need, but not quite.
basically i have a huge spreadsheet with list of items and the date they came in.
I need to count the number of items per week.
At the moment, i currently use =COUNTA(G3:G14)
But this causes problems if I missed an item that came in one week, as i have to add a row in, and then recalute all the “counta” forumlas.
eg.
item date arrived
Test1 15/01/2006
Test2 15/01/2006
Test3 17/01/2006
Test4 18/01/2006
Test5 18/01/2006
Test6 20/01/2006
Test7 21/01/2006
Test8 22/01/2006
Test9 23/01/2006
Test10 24/01/2006
Test11 25/01/2006
Test12 26/01/2006
Test13 27/01/2006
Test14 29/01/2006
Test15 29/01/2006
Test16 30/01/2006
Test17 31/01/2006
So what i want to do is use both forumlas.
I need to be able to look at dates over a year and then work out how many items came in, each week, is this possible?
Hi
Thanks
I was wondering if you could advise on how to perform a sumif between two dates WHERE another condition is met. For example, I am trying to determine the sum between a range of dates where currency = AUD.
Hi
This is a great site.
I have read and tried to apply what you have recomended, but just as posting 2 got a date so did I. Have done what you recomended in posting 3, however I now receive a 0. Does the format of the dates make a differance? I see from the example that you use different date formating in your table to what you use in your formulae.
Thanks
Jonny
Please ignore my previous post, no 30. Issue resolved.
Thank you
Jonny
In sheet Greater Than and Less Than Operator is not working….Pls advise me
Thanks for this solution and I’ve got to be honest, I’m envious that you figured it out. So can you explain why the subtraction? I don’t understand that part, everything else makes sense.
Thanks again,
Mike
G’Day!
What a great site! I love Excel, and I have always told everyone “If there is something you would like to do in Excel, then it CAN be done.”
However, I cannot solve my OWN problem!
I have a spread sheet of data, with column “A” being consecutive dates. What I want to be able to do is run an auto macro that will open the spreadsheet with TODAY’S date selected (and visible) in column A. I have spent yonks trying to work this one out.
Does anyone have any offers of a possible solution?
Regards,
Mark
Mark - Try this in the ‘ThisWorkbook’ module:
Private Sub Workbook_Open()
Application.Goto “index(c1,match(today(),c1,0))”, True
End Sub
I am going nuts. Here is what I want to do…I have a list of dates (column A), list of amounts paid (column B). I want to write a SUMIF or similar function that gives me, for any given line, the total of amounts paid for all dates prior to that date. For line 10, as an example, I want to use SUMIF(A1:A100,
it may have cut off the last bit… SUMIF(A1:A100,
Thank you for providing such a help to me, I appreciate it , and this formula works perfectly
Omg did that help me, thanx very much
I don’t know much about sumif…I have a column of dates and need to subtract today’s date from each getting how many days in between. Can someone please help me? I’d appreciate it. Thanks
Park’s question on post 29 is exactly what I’m trying to do, but
I don’t see an answer. Can you help?
You can do it with a conditional summation (array) formula. For example, if the after date is in cell C12, the before date is in C13, the type of currency desired in C14 and the data (see original post) are in C2:D9 with the type of currency for each data row (i.e. USD vs. AUD) in E2:E9, the formula is:
=SUM(IF(($C$2:$C$9>=$C$12)*($C$2:$C$9
Here’s one way, Mary:
=SUMPRODUCT(‐‐($A$1:$A$3>DATE(2006,1,1)),‐‐($A$1:$A$3<DATE(2006,5,1)),‐‐($B$1:$B$3="aud"),($C$1:$C$3))
Regards,
Jake
Well that didn’t work worth a lick. Here is the complete formula and the rest of the post.
“=SUM(IF(($C$2:$C$9>=$C$12)*($C$2:$C$9
Still not there, last try. Here is the complete formula and the rest of the post.
=SUM(IF(($C$2:$C$9>=$C$12)*($C$2:$C$9″”
Hello All. Just found this site, and have learned a lot today. Thanks for everyones input.
My problem is I have an excel workbook utilizing 4 pages for equities/stock trading.
Sheet 1 lists each trade.
Sheet 2 is a daily total of all trades on a given day from sheet 1.
Sheet 3 is the weekly sheet, which I am having problems with.
I am trying to pull data from sheet 2, giving a refernce to specific days for the given week.
I have utilized the top reference of this page, and it does work, however, I am trying to fill down each colum with this formula and not sure how to autofill the dates so that they change.
=SUMIF(DAILY!B:B,”
More post. Didn’t realize. sorry.
=SUMIF(DAILY!B:B,”
Traderdeath - you have to escape the greater than and less than signs in the comments for this forum. So if you want greater than, use & g t ; (with no spaces) - if you want less than, use & l t ; (with no spaces).
-Jake
hello, Jake,
your post No.43 is exactly what I need, thank you so much for your help, and this website is so helpful for me to fix the problems in my financial report. and if you can explain a lit bit more about the “–” functions in your formula, that will be perfect. I am not quite understand that.
Thank you so much
Ruike
Hi Ruike,
The minus minus syntax simply tells Excel to evaluate the contents as numbers (similar to multiplying by -1 twice). Our array will contain values like {TRUE, TRUE, FALSE} - we want them as {1, 1, 0} so we can do the SUMPRODUCT. Hopefully that makes sense….
Regards,
Jake
Very nice tips here! The “two SUMIFs” method is quite clever, and the “–” method is something completely new for me. Makes my spreadsheets much cleaner and easier to modify. Thanks.
Jake, thanks. I was finally able to get back to this problem & modified your example as follows:
=SUMPRODUCT(–($A$1:$A$3>=$B$5),–($A$1:$A$3
I am making a spreadsheet that formulates the number of projects that are active on the date of a given report.
Example: Report Date: 7/4/06
A B C
1
2 7/4/06
3
4 Project: Start Date End Date
5 Alpha 4/3/05 3/2/06
6 Beta 6/2/06 9/12/06
7 Epsilon 1/1/05 1/1/07
8 Gamma 11/05/05 7/7/06
9 Sigma 5/5/06 8/2/06
10 Theta 2/4/06 6/31/06
The formula cell would read: 4
There are 4 projects that are still active on the date that the report was queried.
I tried using =COUNTIF(A5:A10B2)
Forgive any commas or what have you that I left out. This is just for example purposes. In Excel, I had the fomula down correctly. Since it wasnt working I have gone on to try some others that still havent worked. I tried fomatting the cells so it read Julian numbers. I was still left with 0, or 1/1/1900.
I replaced COUNTIF with SUMIF and that didnt work either.
Is it something I am missing or something a lot more complex like hiding a row that assigns a numerical value to each date then calculate the hidden numbers?
Thanks for any help,
Jonathan
Well, I had my example laid out more like a spreadsheet but I see it was altered in the post. the A column is the project names the B column is the report date and the project start date and the C column is the project end date.
Hope it can be visualized.
Thanks a bunch - just the solution I was looking for!! So Happy, Oh so Happy!
I have a list of people’s ages and I’m trying to count how many are between 3 different categories: under 25, between 26 and 34, and 35 and older. How do I go about doing this?
Tammie: assuming your list is in A1:A7
25 and under - =COUNTIF(A1:A7,"<26")
26 to 34 - =COUNTIF(A1:A7,"<35")-COUNTIF(A1:A7,"<26")
35 and over - =COUNTIF(A1:A7,">34")
The middle one is “count everything less than 35, and subract everything less than 26.
Dick thanks for your response. I attempted putting in that formula and it isn’t working. It’s getting stuck on the quotation marks. Is there any formatting that I need to do?
Tammie: No, it’s just that this blog turns quotation marks into something else and I don’t know how to fix it. Paste the formula into a cell (F2, Control+V), and before you hit enter, delete the quote marks that are there and replace them with normal quote marks.
Dick - I updated your comment to fix the quotes. I’m not sure of an automated way of fixing this, but you can use & q o u t ; (no spaces) instead of ” to make them appear normal in HTML.
-Jake
thank you for your help. It worked.
A B C
2 3/6/06 3/21/06
1 3/7/06 3/21/06
4 2/15/06 3/23/06
2 3/9/06 3/24/06
1 3/7/06 3/24/06
=IF(A:A=2,COUNTIF(C:C,”
I am sure at your level of expertise in EXCEL one of the excel gurus will have solution for this query of mine.
I have a date range in (weekly format) from cell AA1:BZ1: that has values from AA2:BZ2.
In column X1 the header Year to date in this column X2 onwards I want to sum only specified range of numbers from AA2:BB2, criteria being date range from AA1:BZ1
Condition being when a start date and end date is entered in Z2 and z3 respectively, these dates will correspond to the date range in AA1:BZ1
I do I do an average if and median if function.
For example if I have titles and years of service and I want to average the years of service by title and also find the median. I don’t want to do sumif and then divide by the # of employees. I would prefer to have a formula in place.
Thanks in advance.
Guys
YOU SAVE MY LIFE!!! With SUMPRODUCT i found what i wanted.
Now, the two negatives (”–”) what’s that mean??
Hi, could someone help me out?
I have a huge list of date as 09/12/2005, 12,05/2003, etc etc.
Then I would like to use a function which allows me to track the months and display it on another cell.
For example:
09/12/2005 on one cell and December on the other cell.
I tried to apply IF functions but it doesn’t work. It really gives me a headache!
Help please! I’m dying here…
pls can you tell me how to count how many diffrent dates in a clum, exp in a cloum there is differnt dates . amoung the dates i want count how many 2006 records.
pls give me the formula
regards
I have a list of activities with start dates. We use accounting periods, I would like to calculate which period an activity is required by it’s start date. I have the list of start dates in one column, and a table with the year, period, period start and period finish as below
Year Period Start End
2007 10 31/12/2006 27/01/2007
2007 11 28/01/2007 24/02/2007
2007 12 25/02/2007 31/03/2007
2007 1 01/04/2007 28/04/2007
2007 2 29/04/2007 26/05/2007
2007 3 27/05/2007 30/06/2007
2007 4 01/07/2007 28/07/2007
2007 5 29/07/2007 25/08/2007
2007 6 26/08/2007 29/09/2007
2007 7 30/09/2007 27/10/2007
2007 8 28/10/2007 24/11/2007
2007 9 25/11/2007 29/12/2007
2008 10 30/12/2007 26/01/2008
2008 11 27/01/2008 23/02/2008
2008 12 24/02/2008 31/03/2008
How can I work out what period a date is in?
Alan,
Assuming your table is for the range A1 to D16 and the date you’re trying to look up is in G2
=INDEX(B2:B16, MATCH(G2, C2:C16, 1))
Rob
I have a spreadsheet with dates and on each day, there are several activities with start time and end time. I want to add a row between each day, and also to add up the total time for all the activities in each day. Can one of you guru of Excel help by provide a macro for carrying out these functions? Thanks.
I guess I did not explain my problem properly.
I have a spreadsheet with dates and on each day, there are several activities with start time and end time. I want to add a row between each day, and also to add up the total time for all the activities in each day.
The end result should look like the example given below, i.e. a line added between the days and the total time for all the activities for the day is given in the column “total for day”.
Subject StartDate StartTime EndTime Duration(hr:min)Total for day
Activity 1 18/01/2007 13:15 14:30 01:15
Activity 2 18/01/2007 11:30 12:15 00:45
Activity 3 18/01/2007 10:00 11:00 01:00
Activity 4 18/01/2007 11:00 11:15 00:15
03:15
Activity 1 17/01/2007 07:45 08:45 01:00
Activity 2 17/01/2007 09:30 09:45 00:15
Activity 3 17/01/2007 16:10 16:45 00:35
01:50
Activity 1 16/01/2007 12:15 13:15 01:00
Activity 2 16/01/2007 20:50 22:45 01:55
Activity 3 16/01/2007 16:00 16:45 00:45
Activity 4 16/01/2007 14:00 14:40 00:40
04:20
regards
El: please see blog posted 21-Jan-2007 for a solution
That is exactly what I was looking for. You made it so easy. Many thanks Rob.
Looking for a calculation to determine if one date falls between two specific dates to return a specific year.
IE:
I have these dates in my spreadsheet
A
1 9/28/2005
2 10/2/2005
3 2/1/2006
4 9/27/2006
5 9/13/2005
If my given year span is
date span return year
between 9/1/05-12/31/05 2005
or 8/1/06-12/31/06=2006
Looking for a calculation to determine if one date falls between two date spans and return a specific year back to me.
IE:
I have these dates in my spreadsheet
A
1 9/28/2005
2 10/2/2005
3 2/1/2006
4 9/27/2006
5 9/13/2005
If my given year span is:
date span between return this year
9/1/05-12/31/05 2005
or
8/1/06-12/31/06=2006
Hey guys,
Im trying to do some things very similar to what you guys are doing but i cannot apply what I am trying to do with the code samples here.
Basically I have a span of dates in column A
12/01/2006 - 12/19/2006. Then I have two other columns that have start and end dates. I need to make a count of the records that fall within the start and end dates.
for example if the start and end date is 12/5/2006 and 12/10/2006, then the count for 12/5/2006 until 12/19/2006 would be 1 because the start and end date fall in those specific dates in the date span.
12/5/2006 1
12/6/2006 1
…
12/10/2006 1
there are multiple start and end dates… there may be 5 records with a start date of 12/5/2006, so in that case 12/5/2006 would be 5.
So to repeat… I have a list of dates and a list of start and end dates, and I need to count the number of start and end dates that fall in the date span.
Hope that makes sense. If not let me know.
I have a list of dates and values. Is there any way that i can find out how much has been made in a particular month?
eg.
25 Dec 06 £10
01 Jan 07 £20
10 Jan 07 £30
20 Jan 07 £40
05 Feb 07 £50
How much was taken in Jan?
Any help would be appreciated!
A neat solution would be to use an array formula as follows =SUM(IF(MONTH(A2:A6)=1,B2:B6,0)) assuming you have your date range in Col A and values in Col B. As this is an array formula you activate the cell (press F2) and calculate the formula using Ctrl+Alt+Enter.
Note this will sum all January values regardless of the year and you would need to add the year criteria in the same fashion to split them out.
Let’s see, if anyone can help me on this:
B C
Date Amount in EUR
31.12.2006 11355
31.01.2007 11355
31.12.2006 15417
31.01.2007 15417
31.12.2006 11355
31.01.2007 11355
31.12.2006 5250
31.01.2007 5250
cell D17 contains the criteria I want to base the sumif formula on, say 31.01.2007
if I enter the following formula:
sumif(B2:B9;”
the formula i’ve entered is:
sumif(B2:B9;”
Crystal ball gazing a little but what’s wrong with =sumif(B2:B9,D17,C2:C9)
Sascha probably wants a less than or greater than (which is why his comment is being truncated). For that, try
Here’s how I typed that in (with extra spaces):
< code > =sumif(B2:B9, & quot ; & lt ; & quot ; & amp ; D17,C2:C9) < / code >
Looking for a formula to rollup sales $ for todays date -vs- yesterdays date and automatically change the rollup cell amounts as the dates change. Can this be done easily?
A Column = Date and B Column = $
Thanks,
-Paul
ZonaCat, you could use the =Today() function which returns an integer for the current day. Today() and Now() are invaluable when working with dates & times.
Sum to Yesterday
Sum to Today
Hope this helps.
- Ref 84 above, I see now I should have removed the extra spaces, please ignore that one
ZonaCat, you could use the =Today() function which returns an integer for the current day. Today() and Now() are invaluable when working with dates & times.
Sum to Yesterday
Sum to Today
Hope this helps.
Oh f… after all that I got the formulas round the wrong way, Yesterday = Today()-1
I would like a between function that says if AOA in one cell is between A0A - A0O in another cell then return the macthing value in another cell i.e. 6. Is this possible, does anyone know? It would be a big help and huge time saver, I have to enter 1000 values/line items.
POSTAL CODE RATE CODE NOMINAL
A0A-A0O 6 AOA
postal code is on heading, rate is one heading and nominal is another. A0A-A0O is under the postal heading, 6 is under the rate, and A0A is under the nominal heading.
Josh: We need more of the list of postal codes, like five or ten of them. You want a lookup-type formula?
A0A - A0O 6 **
A0P - A0Z 6 ***
A1A - A1N 6
A1O - A2U 6 **
A2V 6 ***
A2W - A9Z 6 **
B0A - B0E 6 *
B0F - B0R 5
B0S 4
B0T - B0Z 5
B1A - B2A 4
B2B - B2F 5
B2G - B4Z 4
B5A - B8Z 6
A0A - A0O would be in one cell; then in another cell I would type in for example A0A which is between A0A - A0O and I want it to return in another cell 6**, which is a rate. So I need a formula that allows me to enter in a combination and just simply have it determine if what value it is between. Another example, I might type in another a second cell B6B, which is between B5A - B8Z and in the last column it will return 6. the one column has to be able to look up between any one of the combinations in the first column, match it with the rate in the second column and give me that rate in the last column? Hopefully, that helps because I know I’m stumped and there has to be a way.
thanks!
Josh, an easy way you can solve this by making a full lookup table of your post code values e.g.
B1A 4
B2A 4
B2B 5
B2F 5
etc.
Then you can use a VLOOKUP function to return the values you require:
Result for B2Z = 4; B2C = 5
Making the fourth parameter ‘range_lookup’ = TRUE means the formula will return the closest match whereas = FALSE will search for an exact match and return #N/A if there is none found.
Unfortunately it seems to be still much more complicated than that, I have close to 1000 postal codes given to me, I have to find the rate that matches by means of a table that is given to me via fedex, ups etc… in which they only give you for example A0A-A0O in one cell, the next cell column =6. So if my A0M is between this then my rate is 6. The main problem is imagine the combinations. A0B, A0C, A0D, A1A, A1B, etc. that are given to me to work with. It seems I have given a really complex question, Im beginning to think that there is no answer though. I will just have to look up the rates for every postal code im given.
thanks
=SUMIF(Data!$H:$H,”
Hey Guys,
I have a filtered list (filtering by regional office). After I have the filtered regional office records, I am then trying to sum the revenue for the resulting records based on the next 90 days. I want to be able to just look at the next 90 days and the sumtotal of revenue from the records that would have a close date within the next 90 days. This would show the forecasted amount of revenue based on 90 days out.
Doug, refer no. 85. Use same method but with Today()-90
Here is a further take on the SUMIF between two dates and adding another element… that the category matches. (actually by adding if’s in the final if’s true value you can have any number of criteria.
The formula has named columns in the source data to simplify it. TranCat - Transaction Category etc.
A B C . . .
1 Category Sum TranCat TranDate TranAmt
2 apples 90 Oranges Jan 1, 2007 $20
3 oranges 20 Apples Jan 2, 2007 $30
Apples Nov 2, 2006 $40
Apples Dec 3, 2006 $50
{=SUM(IF(TransDate >=DATE(2006,6,1),IF(TransDate hit + to get the squigly brackets.
Enjoy
The formating didnt quite go through on the last post. Using periods for spaces.
…A……..B…….C…….
1..Category Sum …………TranCat..TranDate…..TranAmt
2..apples…90…………..Oranges..Jan 1, 2007..$20
3..oranges..20…………..Apples…Jan 2, 2007..$30
4………………………Apples…Nov 2, 2006..$40
5………………………Apples…Dec 3, 2006..$50
Excellent info. on this site. I have managed to fix half my problem! In one column A, I have dates. In column B, I have mostly numbers in the cells, but sometimes text. The sumif formula at the beginning of this thread allows me to check the dates per month in column A and sum the corresponding values in column B. However I also need a formula to check the dates in column A and then count the corresponding times the txt appears for that month. The txt is always the same. So I have:
A B
02 Feb 15
15 Feb quick
18 Feb quick
03 Mar 8
06 Mar 16
So using the sumif and date formula I get the correct answer for summing numbers for Feb and Mar, but cannot return the value of 2 for Feb and 0 for Mar for counting text appearances monthly. Tried nesting SUMIF with COUNTIF and IF with AND to no avail. Would appreciate any pointers!
Can anyone explain why this formula is not working?
=COUNTIF(H:H,AND(”>=”&DATE(2007,2,1)=TRUE,”
Hello I have a problem similar to those posted but I’m trying to sum text isted this is what it looks like can anyone help me thanks
Date Servie Type
1/2/07 Box
1/3/07 Letter
1/4/07 Letter
1/5/07 Letter
1/6/07 Box
1/7/07 Box
1/8/07 Tube
2/1/07 Tube
is there anyway to use the same date forumla and count just the boxes for say january. becuase ihave a long list with months work of stuff and this would really automate things. I know and see from prior post that if the service type were numbers this could be done easily but I cant see to get it done with text thanks
Pivot table (Data menu).
Rows area: Date (remove subtotals), Type
Data area: Type
So far this looks like the original tabel, with an additional Total column that shows the counts of each, which are all 1 in your simple table.
Select the range of dates you want to count between. Right click and choose Group and Show Detail > Group. This gives you a new column, Date2, with an apparently merged cell including the range you selected–it’s not really merged–that says “Group1″; you can overtype this label. Then right click on the Date column and choose Hide.
For a more regular grouping, you can select any cell in the date column, right click and choose Group and Show Detail > Group, and check out the options. You can group by month, year, day (do 7 days for weeks).
HiJohn if that last post was for me thanks. but I didn’t quite understand. I did however find a formula like this that helped me
=SUM(IF(B2:B9=”BOX”,IF(B1C2:C9=”JUNE”,1,0),0))
THE ONLY PROBLEM i HAVE NOW IS THAT IT ONLY WORKS FOR “JUNE” NOT SAY JUNE/1 OR JUNE 30
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
if anyone is able to take a look at this site maybe they can explain that part to me/ If I can use that formula with the date format ie 6/1 6/30 that would be great. Thanks again for any help anyone
Desmond assuming your data above is in 2 columns and starts in A1 try
=+SUMPRODUCT(1*(A2:A9>=DATE(7,1,1))*(B2:B9=”Box”))
if the data above is all text in 1 column try
=+SUMPRODUCT(1*(LEFT(A2:A9,1)=”1″)*(RIGHT(A2:A9,LEN(A2:A9)-FIND(” “,A2:A9))=”Box”))
Another problem slightly different to but related to many I’ve seen here.
I have pairs of dates, say 1/1/2006 and 1/5/2006 as an example, and would like a formula that returned an array which was the count how many days between those dates fell in each of the 12 months of the year. Any suggestions much appreciated. Thanks to all who have posted here.
I have a table with dates in the first column and differentes values in the other columns, How would I do to sum values of any column between two dates?
I have a table with dates in the first column and differentes values in the other columns, How would I do to sum values of any column between two dates that have another condition more in another column?
+sumproduct(1*(Col1>=Date1)*(Col1
Rod
Don’t know what happened with the last post
Try
Q1 +sumproduct(1*(Col1>=Date1)*(Col1=Date1)*(Col1
Rod
Don’t know what happened with the last post
Try
Q1 +sumproduct(1*(Col1>=Date1)*(Col1=Date1)*(Col1
=SUMPRODUCT((date>=A2)*(date
=SUMPRODUCT((date>=A2)*(date”
=SUMPRODUCT((date>=A2)*(date smaller or equal to A6)*amount)
(used text instead of the necessary escape characters)
This is a test ( < )
Going back to the very first solution, is there a way of doing it more dynamically, by comparing the months.
ie, look up the cell range, is the month in each cell equal to the month in another cell, if so do the sum.
Now that would solve my problem.
OK, I just can’t get this to work. I tried all the formulas above but to no avail. My variation on the above is: various customers (”customer code”)with various purchases over time. I want to use a “SUMIF” to reference a customer, say “abc”, search dates within a given range, say, 1/1/06 to 12/31/06, and then sum the purchases during that time. I’ve tried “SUMIF” and “SUMPRODUCT” but can’t get a return. Any ideas based on the short example below? THANKS!
a b c
1 Customer Puchase Amount
Code Date
2 abc 1/2/06 $150
3 def 3/4/07 $150
4 ghi 4/5/06 $150
5 abc 11/4/05 $100
6 def 4/5/07 $100
abc 2005 150
abc 2006 100
abc 2007 0
Sorry I’ll try to make it more legible. Hope this works:
OK, I just can’t get this to work. I tried all the formulas above but to no avail. My variation on the above is: various customers (”customer code”)with various purchases over time. I want to use a “SUMIF” to reference a customer, say “abc”, search dates within a given range, say, 1/1/06 to 12/31/06, and then sum the purchases during that time. I’ve tried “SUMIF” and “SUMPRODUCT” but can’t get a return. Any ideas based on the short example below? THANKS!
a b c
1 Customer Puchase Amount
Code Date
2 abc 1/2/06 $150
3 def 3/4/07 $150
4 ghi 4/5/06 $150
5 abc 11/4/05 $100
6 def 4/5/07 $100
The answer should be:
abc 2005 150
abc 2006 100
abc 2007 0
=SUMPRODUCT(($A$2:$A$6=A8)*(YEAR($B$2:$B$6)=B8)*($C$2:$C$6))
With your summary area starting in A8. Fill down.
Dick-
Thanks so much! It worked great!!!! Looking at your formula, I can see where you got it, but never would have figured it out. The mental logic is a bit different from the “Sum If” structure, but I see it now. This convention will be really useful for a lot of search-and-sum-result formulas. I’ve already put it to use! Thanks again!
Hi I have to match the amount from one column to another like
400 900
500
700 1500
800
900 2900
1400
600
How can I can match data if data will not in series like
400 900
1400
700 1500
900
800 2900
500
600
Refering to Jakes answer on May 26, 2006
What if the dates were between b1-a3, aud/usd were in a2-a3, and the values were
between b2-d2
Sorry I messed up***
Refering to Jakes answer on May 26, 2006
What if the dates were between b1-d1, aud/usd were in a2-a3,
and the values were between b2-d2
Hello, I have a problem with trying to highlight cells in a ‘calendar’ range (columns A1-C3) if the date is between start & end dates (columns D & E) Start & End date list is 24rows. Trying to highlight roster shifts without doing this manually. The cell dates will change per year as i’m using an array calendar.
Hope anyone can help, thanks muchly.
A B C D-start E-end
1/10/07 2/10/07 3/10/07 2/10/07 15/10/07
15/10/07 16/7/07 19/7/07 25/07/07 01/08/07
24/07/07 25/07/07 26/07/07
Okay I go the sumproduct in between two dates (excellent!) but now i need to take it a step further. I need to sum all revenue that was booked between ceratin dates, that departs between certain dates? Is that possible?
Example:
Booked Departed Revenue
1/17/07 7/4/07 $2,555
3/28/07 6/15/07 $3,157
11/27/06 3/21/07 $2,998
I want to sum all revenue for anything booked between 12/1/06 and 11/30/07 and Departs between 12/1/06 and 11/30/07. (Example = $5,712)
Can anyone help?
Thanks!
Hello all great website, i have a problem where i need to compare two dates, then subtract 90 days from the lesser date. the data that im working with looks like this (k1 is the lesser date minus 3 months, m1 is date1, and n1 is date2) bonus points if i can turn “k” data red when the date is within 3 months of lesser date:
k1 = 0308 m1 = 0608 n1 = 0709
k2 = 1207 m1 = 1009 n1 = 0208
hope that makes sense to someone and i can get an answer.
Thanks
David the following will give the answer you’re after, assuming the values are in cells A1:C4:
=SUM(C2:C4*(A2:A4>=DATE(2006,12,1))*(A2:A4=DATE(2006,12,1))*(B2:B4
Dick:
The following will take 90 days of the lesser of dates in columns M and N (assuming you’ve entered actual date values in M & N - otherwise if exactly “0709″ was entered as text you’d also need to convert this to a date):
=DATE(YEAR(MIN(M1,N1)),MONTH(MIN(M1,N1)),DAY(MIN(M1,N1)-90))
Not sure under what conditions you want k to turn red as it would always be within 3 months of the lesser value form the way I read your question?
Pete,
oh thank you for the help, i did have an issue with the “)-90))” i just moved one paren over so formula looks like “))-90)” and now it works like a champ. as for the turn to red i didnt specify that i wanted it red if the result was 90 days from today. no biggie though i will continue to work it from what you gave me. again thank you for the assist.
Cheers Dick,
For the colouring, you’d just use Conditional Formatting on the “k” fields, with the condition being “between” =now() and =now()+90. Then set the result to colouring the text or background red, whichever you want.
Great forum. This has been very helpful. The formula =SUMIF(A1:A9,”
Great forum. This has been very helpful. The formula [at the top of the page] worked perfectly for me except I need to only count values which are greater than 0 (excluding negative numbers) in one instance and include numbers greater than zero in another and can’t quite get it to work. (think calculating the income for a date range and the expensese for a date range so they can be displayed separately by monthly amounts).
Any thoughts? Thanks…
Hi,
Great work.
I have a column with dates in a worksheet and I want the count of rows for every week seperatly.
e.g.
Sl No. id prioritycode open_datetime callstatuscode
1 218881 WIPROCCLG-P2 01/11/2007 14:16 CLOSED
2 218885 WIPROCCLG-P4 01/11/2007 14:18 CLOSED
3 218888 WIPROCCLG-P4 01/11/2007 14:19 CLOSED
4 218928 WIPROCCLG-P2 01/11/2007 15:09 CLOSED
5 219707 WIPROCCLG-P3 02/11/2007 12:20 CLOSED
6 219810 WIPROCCLG-P3 02/11/2007 13:37 CLOSED
7 219950 WIPROCCLG-P3 02/11/2007 15:55 CLOSED
8 220034 WIPROCCLG-P1 02/11/2007 16:58 CLOSED
9 220835 WIPROCCLG-P1 03/11/2007 13:17 CLOSED
10 220862 WIPROCCLG-P1 03/11/2007 13:34 CLOSED
11 220888 WIPROCCLG-P3 03/11/2007 14:12 CLOSED
12 221976 WIPROCCLG-P2 05/11/2007 11:20 CLOSED
13 221998 WIPROCCLG-P2 05/11/2007 11:29 CLOSED
you are sure great, have used the sumif method for my excel problem, thanks a bunch!
Need to count the number of records that fall between two dates and meet the criteria of another column which is text. EX Find all vehicles to expire between 01/01/07 and 01/01/08, that are red. Please help
I need help. This looks like a great place for good info, however all of the above examples are similar to what I require but I don’t seem to be able to crack it.
I have a date range in Column N starting at 15/03/2007 (UK Eng) and ending 13/03/2008 (UK Eng), I also have column BK which has various text e.g. End to End New, End to End relocation and about 10 other variations. I am trying to create a Chart to show all the End to End jobs between and inclusive of 15/03/2007 and 14/04/2007.
For instance there are 15 End to End new and 10 End to End relocations between these dates, some of which are on the same date se example below.
Dates (N) Type (BK)
15/03/2007 End to End New
17/03/2007 End to End New
17/03/2007 End to End New
17/03/2007 End to End Relocation
19/03/2007 End to End Relocation
19/03/2007 End to End New
19/03/2007 End to End New
Etc.
I will need to add to these dates from time to time and would like the chart to flow automatically. Therefore I am unable to use what I do at present; I am using this formula at the moment.
=COUNTIF(’Sheet1’$BK386:$BA$406,’Management Reports 2008′!A44)
($BK386:$BA$406 being the row range of column BK)
(’Management Reports 2008′!A44, being told what to look up)
If anyone could help me with this I would be truly grateful, as my hair, what’s left of it is turning grey.
Regards,
I have a question about totaling by month. I built a spreadsheet with several columbus tracking events by name and type. I want to be able to total all columns that match a name, type and occured during a certain date. Right now I am limiting the count manually by selecting a range for my count. I want to have it do it automatically by month as the year progresses. The formula I am currently using is
=SUMPRODUCT((C30:C503=”WRHMR”)*(D30:D503=”40K”)*(L30:L503>0),H30:H503)
Column C has the item description from a drop down
Column D has the item type from a dropdown
Column L has a date and this is the one I want counted only by the current month
Column H is the column I total
What I want is to do is only count items from Column L that occur in the current month.
What is excel formula for counting the number of cells containing data in between two dates?
Try this array formula:
=SUM((mydates>=date1)*(mydates=date1),(mydates
Sorry.
Try this array formula:
=SUM((mydates>=date1)*(mydates=date1))
Regards.
I need something very simular to this but the date is in a cell and the I want to copy the formula down. Instead of &date() I want sheet1!E@ and sheet1!E3 for the criteria. But Excel hates me. Anyone have a suggestion?
Great tip on the SUMIF between 2 dates. Anyone have an idea how to get the MAX value between 2 dates? Subtraction in this case doesn’t work because it subtracts the two maxima.
I tried to use the first formula, but it doesn’t work for my data. here’s the situation:
Dates Total
04-Feb-08 1500
12-Mar-08 1500
27-Feb-08 1500
If I apply the first formula given [=SUMIF(A1:A3,”=”&DATE(2008,2,1),B1:B3)-SUMIF(A1:A3,”
Sorry, it looks like the information was saved incomplete. According with the previous example (Dates & Total), I need to sum the totals during February. Working with that simple example, the total should be 3000, but with the first formula given here, I get either -1500 or 1500, I can’t get the real total.
Any ideas?
I have to say thanks again for the “sumproduct” formula earlier! Wow! What a Godsend! I have been using them in a lot with my work. What a useful Excel formula! Now I have a different problem and don’t know if this is the right place.
Say I have a list of customers. I want to find any given one’s last purchase date (or as a variation, the last purchase amount.) On the list will be lots of customer names, many repeating, with various dates and purchase amounts. Let’s say:
A1 Date / B1 Customer / C1 Amount
a2 4/10/05 Joe $5000
a3 5/10/05 Bob $1000
a4 6/10/05 Joe $2000
a5 6/10/06 Bob $3000
a6 6/15/06 Joe $2500
a7 3/10/07 Joe $5000
So, how to find by entering “Joe” his last purchase date was 3/10/07? Variations of that could then be made to find out what the amount was…
I just don’t know where to begin…
Thanks anyone!
Range of data on A2:B7 and criteria on E2
=INDEX($C$2:$C$7,MATCH(1,INDEX(($B$2:$B$7=E2)*($A$2:$A$7=MAX(INDEX(($B$2:$B$7=E2)*($A$2:$A$7),0))),0),0))
Regards
Or the Array version,
=INDEX($C$2:$C$7,MATCH(1,($B$2:$B$7=E2)*($A$2:$A$7=MAX(($B$2:$B$7=E2)*($A$2:$A$7))),0))
Confirm with Ctrl+Shift+Enter.
Wow! That works! THANKS, Elias! I only tried the first solution. Works great! (I’m sure the array will work fine too, then.) Thanks! I think I follow it, though I’d never be able to work that out myself.
One problem I didn’t foresee- if there is more than one entry for the same date, such as a6, a7:
a2 4/10/05 Joe $5000
a3 5/10/05 Bob $1000
a4 6/10/05 Joe $2000
a5 6/10/06 Bob $3000
a6 6/15/07 Joe $2500
a7 6/15/07 Joe $5000
This formula stops at the first entry (a6 in the above example) not the last. I tried changing that last “zero” in the formula to “1″, and that SEEMED to work, but there were a couple of cases where it returned a strange number (and the wrong one) so I know that’s not the solution… Any thoughts? Thanks again! -Gary
PS: I successfully adjusted it to so as to find the last date of any order.
I’m glad you got it.
I reread what I wrote and can’t believe I wrote the exact opposite of what I meant. I have NOT figured out how to get the LAST date. I meant I figured out how to get the amount or date from the formula that was set up to give the name. But it stops at the FIRST date or first occurrence. Can it be adjusted to go the last date if there are multiple entries of the same date?
Sorry for the confusion. Can’t believe I wrote exactly the opposite of what I meant. No wonder you said “Glad you got it.”
_Gary
Try any of these.
=INDEX(C:C,MAX(INDEX(($B$2:$B$21=E2)*($A$2:$A$21=MAX(INDEX(($B$2:$B$21=E2)*($A$2:$A$21),0)))*(ROW($C$2:$C$21)),0)))
or Array version.
=INDEX(C:C,MAX(IF($B$2:$B$21=E2,IF(A2:A21=MAX(IF($B$2:$B$21=E2,$A$2:$A$21)),ROW($C$2:$C$21)))))
confirm with Ctrl+Shift+Enter
I’m not a native English speaker so there could be some part of misunderstanding on my side.
Regards
Guys i’m really hoping someone can help me…I’m struggling with finding the right formula to do what i’d like.
I have a worksheet with a column of dates in it (B:B), the next column along has a list of names in it (C:C).
On a second worksheet i have a start date (B5) and a finish date (C5).
What i would like to do is to reference the start and finish dates to create the array to lookup in on the first sheet in column B, then return the name that is in the next colum along (C). Another problem might be that the same name might appear more than once between the start and finish dates. So if possible i’d like to also know a way of picking up the second name and possibly the third too…! (there will be no more than 3 for sure).
Hopefully that make a nit of sense, if not than i am more than happy to send an example sheet to someone.
Regards
I need some help de-bugging my formula. I have my main worksheet that I am using the Sumif Between Dates forumla and linking the formula to a separate worksheet. It works fine as long as I have the 2nd worksheet open but it will not stay linked if the 2nd worksheet is closed. I get a #VALUE! error. Does anyone have any thoughts on this?
Here is my formula:
=SUMIF(’\\rfdpfs090\pmiswap\Common\Rail Traces\Alfalfa - Fort Worth\[_2008 Alfalfa - Fort Worth Turn Time.xls]2008′!$B$4:$B$105,”
My formula was cut off:
=SUMIF(’\\rfdpfs090\pmiswap\Common\Rail Traces\Alfalfa - Fort Worth\[_2008 Alfalfa - Fort Worth Turn Time.xls]2008′!$B$4:$B$105,”
Elias-
I wanted to get back and say “many thanks” for your solution. It worked great!!!!!!
I’m still trying to figure out the nuances of the formula for future reference. I don’t know how you do it! Thanks again! -Gary
Adrian, I think I am running into the same problem that you are. I have a column of dates (with some repeat dates) that are associated with a set of values. My formula won’t pick up the values for the repeat dates, only the first unique date. For example, for the sum for January 2007 in the list of data below, I get 6, instead of 9. Thoughts?
1/5/07 - 5
1/5/07 - 3
1/6/07 - 1
I’m using MS Excel 2002, why doesn’t this work?
=COUNTIF(G2:G139,””&DATE(2006,12,31))
Hi,
This will be easy for most of you I should think, but very new to me!
I just need to set a spreadsheet up and say if the date is between 1st Jan and 31st March, this is Q1, 1st April to 31th June, Q2, etc.
Any help very much appreciated!
Geko.
Geko:
Assuming the date is in cell A1
=INT((MONTH(A1) - 1) / 3)
Try this,
=LOOKUP(MONTH(A1),{1,4,7,10},{”Q1″,”Q2″,”Q3″,”Q4″})
Regards
Other way,
=”Q”&MATCH(MONTH(A1),{1,4,7,10})
Elias and Rob - thank you both.
Elias, what is the 1,4,7,10 refering to? It worked a treat but I’ve no idea how!
Geko - that maps months to calendar quarters. If the “month” (a value of 1 to 12) is greater than or equal to (GTE) 1 and less than (LT) 4, you want it to return “Q1;” GTE 4, LT 7 return “Q2;” GTE 7, LT 10 return “Q3,” etc. The lookup function works by comparing the first argument to the values in the second (array) argument, and returning a coresponding vaue in the third (array) argument. You can look in the Help files, either for lookup or hlookup / vlookup - which work the same way, but take ranges for the second and third arguments.
I have three columns; “user id”, “date time” and “On/Off” with over 6000 lines of data. I need to determin if a user has logged off from a single day to mulitple days and times. If they have not logged off then use the latest date/time logged on. If they have logged off (multiple days times) then determine the amount of time they were logged on for that period. Then to import the resulting data into Access to produce a report.
I have ‘date’ in column A (from 01/01/09 to 31/12/09)
I have ‘day’ in column B (from Monday to Sunday) next to it’s corresponding date (in column A) i.e….
01/01/09 Thursday
02/01/09 Friday
03/01/09 Saturday
04/01/09 Sunday
05/01/09 Monday
06/01/09 Tuesday
07/01/09 Wednesday
08/01/09 Thursday
09/01/09 Friday etc etc
And would like to count the number of times each day appears in a specified date range…
From To Monday Tuesday Wednesday Thursday Friday
01/01/09 09/01/09 1 1 1 2 2
Thanks
Jack
Hi, I’m looking for help on horizontal sum. For example, columns A, C, E and G contain values($); I labelled them all “amount”. Columns B, D, F ad H contain date and labelled as “date”. So if column B and D have dates entered, then the formula should sum the value in columns A and C. Can anyone help?
Hi,
This time I’m looking to fill a cell with one colour for “yes” and another colour for “no” - is this possible, and if so, any ideas how?
Geko
Need a formula for summing column of numbers bewteen two dates. SUMIF works great if you know the dates; however, I want to enter a start date and end date in other cells and have those dates automatically inserted into the SUMIF formula so the result can be customized for whichever dates user chooses
rickey,
just replace Date(x,y,z,) with a cell reference.
Geko,
Look into conditional formatting.
I am also looking for the same quick solution to count the total of numbers of rows selected between a speficied date range.
I used the “Conditional Sum” in tool option, it works! The only pain is Excel ONLY work if you use the mouse to select your condition & cells. You CANNOT COPY or TYPE in the cell from one to another.
I have 12 months rows to do, so I have to do the “Condition SUM” 12 times ! Please advice if someone has a better solution.
The formular will look like this after you use the mouse to select your range:
{=SUM(IF($A$2:$A$20>=DATEVALUE(”1/01/2008″),IF($A$2:$W$20
Alvin:
Sorry, The formula was cut off …
I am also looking for the same quick solution to count the total of numbers of rows selected between a speficied date range.
I used the “Conditional Sum Wizard” in tool option, it works! The only pain is Excel ONLY work if you use the mouse to select your condition & cells. You CANNOT COPY or TYPE in the cell from one to another.
I have 12 months rows to do, so I have to do the “Condition SUM Wizard” 12 times ! Please advice if someone has a better solution.
The formular will look like this from the “Condition Sum Wizard” :
{=SUM(IF($A$2:$A$20>=DATEVALUE(”1/01/2008″),
IF($A$2:$W$20
Hi,
I have the formula
=LOOKUP(MONTH(C12:C101),{1,4,7,10},{”Q1″,”Q2″,”Q3″,”Q4″})
and need return a blank when no date has been entered. Can I insert a VLOOKUP function within this?
Thank you Mark for the conditional formatting tip - easy peasy!
Geko
Hi,
This is what I am looking for. Any help would be greatly appreciated. i have one date, lets say june 15 2007. On another sheet I have a list of dates, several for each month of the year for several years. I would like to find the closest date from the list prior to the date mentioned (june 15 2007) and the closest date after the date mentioned (june 15 2007). this does not need to be one formula. I can have one column PRIOR and another AFTER. I have tried vlookup but no luck.
Thanks,
Al
I am looking to count the number of times a product has been purchased between two dates. Each product and its purchase date are columns in a row. i.e.
ColA ColB
ProductA Jan 6,2008
ProductB Jan 8,2008
ProductA Feb 1,2008
ProductA Feb 25, 2008
I have not been able to get the countif or countifs functions to work.
Thanks, Tom
I’m trying to count how many times a certain type of request was done between two dates.
Here’s what I have for a sheet
Date Submitted Request Type
9/1/2008 New Equipment
9/4/2008 Rebuild Equipment
9/10/2008 New User
9/10/2008 New Equipment
I’m trying to count the number of times “New Equipment” occurs between 9/1/2008 and 9/30/2008
I can get the count of items using: =-COUNT(A:A)+COUNTIF(A:A,”>=9/1/2008″)+(COUNTIF(A:A,”
Here’s the rest of my formula I’m using now
=-COUNT(G:G)+COUNTIF(G:G,”>=6/1/2008″)+(COUNTIF(G:G,”
Ron: You can’t type less than signs in the comments because it thinks it’s HTML. Read the note above the comment box to see how to escape those characters.
You guys sure know your stuff!
Could any of you help me with the following:
I would like to determine the number of times a given day occurs between two dates.
Example:
Start date: 04/03/2008
End Date: 24/02/2009
Looking for the 16th of each month
Answer should be 12
When looking for th 25th of each month
Answer should be 11
Here’s hoping one of you gurus know how I can do this, I will be extremely happy.
Thanks in advance.
OCTOBER 11, 2008
DEAR SIRS,SAW YOUR AD ON THE INTERNET THEN HAD ENCOURAGEMENT TO ASK YOU QUESTION AS FOLLOWS;
I USE EXCEL COMPUTER PROGRAMME
IN CELL A1, I HAVE MY DATE OF BIRTH SAY 12/04/1955
IN CELL B1, I HAVE CURRENT DATE SAY 11/10/2008
I WANT TO KNOW EXACT DIFFERENCE BETWEEN TWO DATESSAY DAYS/MONTHS/YEARS
COULD YOU PLEASE LET ME KNOW FORMULA TO COME OUT OF THIS PROBLEM
THANKS IN ANTICIPATIONWITH
BEST REGARDSASHFAQE-MAIL; ziaurrehman43@yahoo.com
sir
i want to use formula of ‘if’ and ‘and’ . can u solve my problem .
yours truly
Muhammad Asim Amin
Sir,
I have created a bank ledger for the ease of my company to use, but I am trying to track spending during a range of dates that are spent against a certain item. For instance…
A B C
1/1/08 Account 1 $1200
1/4/08 Account 2 $1200
1/16/08 Account 1 $2000
2/3/08 Account 2 $400
2/12/08 Account 1 $3000
2/20/08 Account 2 $1340
3/1/08 Account 1 $3400
…I want to know how much was spent from Account 1 during Jan 2008. I have been racking my brains out about this one for sometime now and all the arguments I come up with do not lead me to the answers I am looking for. Please help me. I am using office 2007 and if this is the issue please let me know about that as well.
Thank you for you help in this matter.
Joseph — Try using the SUMPRODUCT() function. This appears to work for me:
=SUMPRODUCT(–(TEXT(Date_Range,”yyyymm”)=”200801″),–(Account_Range=”Account 1″),Amount_Range)
You could even put the “200801″ and “Account 1″ into cells, so that the function can vary simply by changing the cell values.
qns0mk7n1qq7r1i1
I am trying to build a workbook to help employees relate paydates to their personal bill due dates. say B:B would be amount of the bill, C:C would be day it is due and ROW 1 would be pay dates. I am trying to figure out any way to have the system return the bill amount if the due date is greater than the current paydate but less and or equal to the next pay date. Because the due date is only a DAY, no month or year, and the pay dates are full dates I am running into errors. could use some help or even “Not going to happen” so i stop working on it.
thanks
Randy,
I tried the formula that you provided but I could not get it to yeild the result I am needing. Below I have posted a mock portion of one of the pages. I hope it posts like I see it now. But if it does not this is the best I can describe it. Cell B1 is “Date”, Cell C1 is “Transaction Decription”, and Cell D1 is Payment Fee/Withdrawal. I have two Cells B2:B3 Merged, I have Cell C2:C3 split, and I have D2:D3 Merged. The reason I have done this is because Cell C2 is a drop down menu so the employee can choose from a list of knowns and he or she can type a description in Cell C3. Rows 4:5, 6:7…. are set up in a simular manner. I have Row 1 Frozen so it is always displayed. What I need is a formula that will take the date range of January 01, 2008 - January 31, 2008 and add only Payment Fee/Withdrawal where Utilities, Credit, or Phone are chosen from the drop down menu. If the formula you provided is one that I can use, I guess I am just doing it wrong. Please advise me on the best course of action. The formula will also be on a second Sheet.
Date Transaction Description Payment Fee/ Withdrawal
1/1/2008 Utilities $62.12
Water
1/1/2008 Utilities $25.24
Columbia
1/1/2008 Credit $295.00
Discover 1
1/1/2008 Credit $130.00
Discover 2
1/1/2008 Credit $175.00
MBNA
1/1/2008 Credit $63.00
Lowes
1/1/2008 Phone $68.74
Verizon Home
Thank you again for your help.
Hi,
Thanks to all the posters here.
Does anyone know how to apply the (far) above SUMPRODUCT formulae to filtered lists? It doesn’t seem to nest within SUBTOTAL.
-N
Joseph — Having trouble posting here. But something like this should do what you want:
=SUMPRODUCT(–(TEXT(Date_Range,”yyyymm”)=”200801″),
(Account_Range=”Utilities”)+(Account_Range=”Credit”)+(Account_Range=”Phone”),Amount_Range)
Another alternative would be to use the DSUM() function.
Randy,
I am slightly a novice at this and nothing that you have typed is giving me the results that I am looking for. I know this is annoying to you who obviously have a better understanding of ecel than I do, but can you use the example I have typed above within the formula that you have typed. I am not sure what I am doing wrong but excel just gives me the Name error.
Thank you again for your help and I hope that this will be the last question I have on this paticular topic.
Joseph
This works fantastic for summing values that occurred x number of days in the past. You don’t even need to use the DATE(x,y,z), you can use cell references, e.g.
=SUMIF($A$1:$A$33,”
Joseph — #NAME? errors would most likely be because you are not filling in ranges for my substitutes for the ranges: Date_Range, Account_Range, and Amount_Range. Each of those would have to point to the corresponding range in your database — for the dates, account names, and amounts.
This is a wonderful blog. Thank you so much for sharing. Is there any way to run this formula so that it references the dates from specific cells outside the formula?
For example, rather than changing the dates in the formula each time, I want to place the dates in two cells that the formula will reference. As I change the dates, I am hoping the sum will change as well.
Excel seems to be stumbling because it requires the dates to be spelled out in (Year,Month,Day) format within the actual formula.
MSNovice: That’s not required, it’s just easier to read in an example. For your case, you just use (with your dates in G1 and G2):
Dick Kusleika: Thanks again. I played around and found a way to return the sum by converting the Date to Text. Using the original example (with the From Date in B14 and the To Date in B15):
=SUMIF(A1:A8,”>=”&TEXT(B14,”D-MMM-YY”),B1:B8)-SUMIF(A1:A8,”>”&TEXT(B15,”D-MMM-YY”),B1:B8)
I have a years worth of daily sales data for several hundred unique part numbers. The file includes the PART NUMBER, TRANSACTION DATE and TRANSACTION QTY. I’d like to get the monthly total for each part number on a seperate sheet. I currently seperate the data by month and use SUMIF for each part number. Can anyone recommend a better way? Your assistance is appreciated!
In a spreadsheet I have a column (H) with the amount of fees invoiced to clients. In another column (O) I have the date it was paid. I want a formula that will enter the fee value from column H into column W, if the date range is from the 1st July 2008 to 31st July 2008. I can then replicate this in eleven other columns, ad them up at the bottom and gain a monthly summary of what was paid for each month. Can anyone help with a formula?
I have a spreadsheet with contract information and I would like to note whether a contract is current based on today’s date falling on or in between the dates listed in the Start Date and End Date columns.
Hi there!
Thanks for your blog. I am having an issue here whether to use COUNTIFS, SUM, OR.
Here it is:
Req No. Division RequestDate EntryDate ChangeDate
1 A 01/01/2009 01/01/2009
2 A 03/01/2009 06/01/2009
3 B 24/01/2009 24/01/2009
4 A 26/01/2009 04/02/2009
5 A 29/01/2009 01/02/2009 03/02/2009
6 C 31/01/2009 31/01/2009
7 A 31/01/2009 03/02/2009
8 A 02/02/2009 02/02/2009
9 B 06/02/2009 08/02/2009
Each division send request, which are registered when received (RequestDate), then updated when entered in the system (EntryDate) and if necessary changed (ChangeDate).
I need to count the number of requests carried forward from one month to another for a special division (say “A”). For this I refer to the request date (e.g. all request entered in January’09) with EITHER an entry date OR a change date in the next month (february’09).
in the table above it will be 3 requests: Request No.4,5,7.
However Request No 5 has both a EntryDate in february AND a Change Date in february. I do not want to count this request twice!
Here is my attempt (not really working…):
For all with entrydate in february: =COUNTIFS(D:D,”>=01/02/2009″,D:D,”=01/02/2009″,E:E,”=01/01/2009″,C:C,”=01/01/2009″,C:C,”=01/02/2009″,D:D,”=01/01/2009″,C:C,”=01/02/2009″,E:E,”=01/01/2009″,C:C,”=01/02/2009″,D:D,”=01/02/2009″,E:E,”<=28/02/2009″)
Mmmm… Any suggestion to have it a bit simpler (as in reality I am also linking to several external workbooks…which I am not going to include here!)
Thanks a lot in advance!
K
Can somebody please help an old man. I’m not a novice to computers as I started with a Radio Shack TRS-80 and even wrote some basic code back in the day. The technology passed me by fairly quickly and now I just work with macros or complicated formulas only when I must. A minister friend asked me for some advice and I said, “Sure, no problem.” Stupid me, now it is a problem. Any help would be greatly appreciated.
I’m trying to total data by month between various entry dates. I’ve been wrestling with SUMIF, SUMIFS, LOOKUP, etc without sucess. Here is the worksheet and the formula that I think comes closest but still doesn’t really work.
A=Date B C G
1/1/09 10 5 40
2/1/09 20 10
1/7/09 30 15
Column A is the date range formated as indicated. Columns B thru F are the data entries. I want the cell at G2 to total all the January entries. If I use the following fomula:
=SUMIF(A1:A3,”<=”&DATE(2009,1,31),B1:C3)-SUMIF(A1:A3,”<”&DATE(2009,1,1),B1:C3)
I get 40, when the figure I am looking for should be 60.
I can’t figure out what I’m doing wrong. Please help … you’ll be old someday too.
Much thanks,
Richard
Hi Richard -
Use SUMPRODCUT. This works
=SUMPRODUCT((MONTH(A2:A4)=1)*B2:B4)+SUMPRODUCT((MONTH(A2:A4)=1)*C2:C4)
See http://www.xldynamic.com/source/xld.SUMPRODUCT.html
…mrt
…mrt
Hi K -
After I stopped wrestling with 24, 26, 29 and 31 months in this year
I put your data in A17:E26 (ie A17 = “Req No.) and this formula returns 2:
=SUMPRODUCT((B18:B26=”A”)*(MONTH(C18:C26)=1)*(MONTH(D18:D26)1)*ISBLANK(E18:E26))
It multiplies the truth table: cells = to “A” times request months = January times entry months January times changes blank, and sums.
…mrt
Drat -
that should be entry months not equal to January
…mrt
Michael, thank you so very much for your SUMPRODUCT solution to my date range problem. It worked perfectly. Thanks again for taking the time to help.
Richard
Richard -
You’re welcome. I’m already old, and working on older day-by-day
…mrt
Thanks to all your suggestions! You guys are Excel pros! =D
I actually tried all of them, and the quickest are TEXT TO COLUMN and DATEVALUE-SUBSTITUTE.
PAYMENT DATE PAYMENT DATE=CEL B CUST NAME CEL A DEL DAYS = C
4/18/2009
4/12/2009
3/25/2009
4/1/09
4/26/2009
5/18/2009
HI THERE, I LOVE THIS BLOG IT IS FUN AND EDUCATIONAL SO HERE IS MY EXCEL SOS
DEL. DAYS
23
16
24
13
26
19
CUST NAME
LOPEZ
JOHN
JOHN
LOPEZ
SAM
SAM
1- I WANT A FORMULA ON F COLUMN TO FIND EACH NAME LISTED
2- I WANT ON COLUMN G TO CALCULATE THE AVERAGE FOR THE MOST RECENT MONTH FOR EACH CUSTOMER from column D DEL. DAYS
THANX A LOT, GOD BLESS
badara
Hi Badara -
At the top, DEL DAYS are in C, and at the bottom, in D. Which is it?
And I don’t understand what you want to average. Please show a proper calculation for Sam, John, and Lopez.
…mrt
This has almost nailed what I am after except I would like it to look for my “criteria dates” by referencing a cell containing the date therefore the
“<=”&DATE(2004,4,2)
part doesn’t work for me.
what I’d like it to do is look at the cell contaning my date eg cell C3 contains 08th July 09 and find anything between that and the last 7 days (including the 8th)
Any ideas?
Cheers
Sorry that bit above should read “the 7 days preceeding (and including) the 8th July” and not “the last 7 days” just to avoid confusion
Gogs -
Make sure all your dates are numbers. You could make a helper column set equal to your column of interest and set the number format of the helper column to General. Anything that doesn’t look like a 5-digit number is text in the original column. You need to fix those.
Assuming all then are numbers, then if C3 is a true date, and not text, “<=”&C3 should work. If C3 is text, and in a date format Excel knows, “<=”&DATEVALUE(C3) should work.
…mrt
Hi Michael,
Works a treat. Thanks!!
Gogs
I’m sure there is a simple answer to my question, but a formula really has me baffled.
I want to ask a cell to display number 1 when another cell [same file different worksheet] shows any of the following numbers 31,32,33,34,35,36,37,38,39,40. Any help very much appreciated.
Working with excel 2007
Hi Val -
You didn’t specify what to show when the values are not OK, so I’m assuming to show nothing.
Let’s say the other cell is Sheet2, cell A1, In your cell of interest:
=IF(AND(Sheet2!A1>=31,Sheet2!A1<=40),1,”")
1 is the value for the TRUE condition. If you wanted to say something different for the FALSE condition, put it after the last comma. It can be another formula, including another IF(), a number, or text. If text, put it inside double quotes.
…mrt
Hi Michael
Thank you so much for your reply, but I think I had better try and explain what I am trying to do. I am organising a dog agility show with 17 classes. There is a worksheet for each class with a cell on each which counts the number of dogs as I enter their names on the sheet. Each class [on competition day] awards rosettes to 3rd place regardless of how many dogs were entered, however when the number in a class goes over 30 but under 41 we award to 4th, over 40 under 51 to 5th and so. I wanted to be to see at a glance my rosette count and basically have the number ready for ordering the moment we close entries. For instnace how many 7th place rosettes would I have to order. I do appreciate I could just look at the cell containing the class entry but by the time we reach the close of entries I have so many numbers going round my head I have made mistakes and not ordered enough rosettes. I have also tried to design it so that the final numbers are automatically placed on the order form. We are talking approximately 600 dogs all needing ring numbers and running orders. Normally I will sit at something like this and experiment until I find the way, but I am even finding the ‘help’ section confusing because I really do need to be told something in laymans terms. Once again, many thanks.
Hi Val -
In my mind a see a master spreadsheet followed by 17 more. On each of those 17 is a cell with a count of entries on that sheet. Presumably the same cell of each sheet.
You order 17 1st’s, 17 2nd’s and 17 3rd’s. You can’t order a 5th without ordering a corresponding 4th. So if you count the number of cells greater than 30 you will have the number of 4ths. Count the ones greater than 40 and you will have the number of 5ths. Count the number greater than 50 and you have the number of 6ths. Etc.
This is the type of thing COUNTIF would do for you if COUNTIF worked in 3-D ranges. It doesn’t, but here in DDoE is a user defined function that is a COUNTIF3D. See
http://www.dailydoseofexcel.com/archives/2006/02/25/3d-user-defined-functions/
If you don’t want to use a UDF, I’ll have to think some more
…mrt
OK -
Thought some more. Assume on each class’s sheet, cell A1 has the count of entrants.
A2: =IF(A1>30,1,0)
A3: =IF(A1>40,1,0)
A4: =IF(A1>50,1,0)
A5: =IF(A1>60,1,0)
A6: =IF(A1>70,1,0)
A7: =IF(A1>80,1,0)
etc on each sheet.
On the Master sheet; in the 4th place cell =SUM(CLASS1:CLASS17!A2)
Do the same for a many rosettes as you award.
…mrt (no UDF)
Please excuse my bad manners, should have replied sooner but bit busy at the moment. Many thanks for all your help. Will spend time today working with the advice you have given. Again, thank youl
Val
Guys,
Looked through the blog but nothing quite matches what I’m looking for. I have a sheet where Col A is populated with dates which are changed everytime the sheet is updated. In Col B there are dates which increment weekly. In Col C, I want to count the number of occurences in Col A between 2 dates in Col B but only between one cell and the one immediately below it. I can use countif but that means I have to enter the dates manually in the calculation in Col C, like this: =SUM((COUNTIF($F$2:$F$46,”>13/6/2009″)-COUNTIF($F$2:$F$46,”Q3)*($F$2:$F$46<Q4) where Q3= 13/06/2009 and Q4 = 20/06/2009
Here’s the sheet example:
Col A Col B Col C
03/07/2009 05/07/2009 2
04/07/2009 12/07/2009 3
06/07/2009 19/07/2009 1
07/07/2009 26/07/2009 0
07/07/2009
14/07/2009
Thanks in advance
Sorted it myself, missining a set of parentheses from =SUMPRODUCT(($F$2:$F$46>Q3)*($F$2:$F$46<Q4))
and all works fine
I need some help…
I am trying to create a formula that basically states that:
if the start date (col D) falls between 01/01/07 and 03/31/07, then the credit amount (col H) is true, otherwise leave blank. In the example below, I should see in output $3,000 as the start falls between those conditions.
A B D E F G H I
Post Date Start End Contract $
1 Invoice 01/11/2007 01/11/07 01/10/08 $3000.00 3,000.00 3,000.00
2 Invoice 01/11/2007 01/11/07 01/10/08 $3000.00 250.00 2,750.00
3 Invoice 02/11/2007 01/11/07 01/10/08 $3000.00 250.00 2,500.00
4 Invoice 03/11/2007 01/11/07 01/10/08 $3000.00 250.00 2,250.00
Thanks for the help in advance!
Hi Krista -
As I parse your data, I get this:
Col A:
Post
Invoice
Invoice
Invoice
Invoice
Col B:
Date
1/11/2007
1/11/2007
2/11/2007
3/11/2007
Col C:
Empty
Col D:
Start
1/11/2007
1/11/2007
1/11/2007
1/11/2007
Col E:
End
1/10/2008
1/10/2008
1/10/2008
1/10/2008
Col F:
Contract
$3,000.00
$3,000.00
$3,000.00
$3,000.00
Col G:
$
3,000.00
250
250
250
Col H:
3,000.00
2,750.00
2,500.00
2,250.00
What Cell do you want to return TRUE based on the start date? And where do you want the output of $3000? I note that all four invoices meet your conditions as I understand it, but I think I’ve got it wrong.
…mrt
Hi Michael,
Column G:
-
$250
$250
$250
Column H:
$3,000
-
-
-
Maybe a better way to put it is; if there is a value in column H, and the start date falls between 1/1/07 and 3/31/07, then output should equal value in the H column (output to be in J1). Therefore, based on a spreadsheet, there should only be 1 output and it should be equal to the $3,000
Hopefully that makes more sense?
Thanks for your help,
Hi Krista -
OK, try this in J1:
=IF(AND(D2>DATEVALUE(”1/1/07″),D2<DATEVALUE(”3/31/07″),H20),H2,”")
I’m using a literal “falls between.” If 1/1/07 and 3/31/07 are really OK, change the AND() inequalities to “greater than or equal to” (>=) and “less than or equal to” (<=)
Format J1 as currency.
Read the AND() as a three-way test: Start must later than Jan 1 2007, End must be earlier than Mar 31, 2007, H2 is non-zero.
All three must be true for the AND() to return TRUE, and the IF() returning H2. If the AND() returns FALSE, the IF() returns empty text.
…mrt
OPPS…that’s
E2 < DATEVALUE(”3/31/07″)
in the middle of the AND()
Why do I always see these things after pressing the Submit button ;-(
…mrt
OOPS#2 -
Not my fault! There’s in inequality operator between the H2 and the 0 at the end of the AND()
Between the hydrogen and the oxygen? Really?
…mrt
Hi there!
Anyone can help me please.
I am trying to count date of column B base on Column A.
eg.
Column A Column B
Date Test Date Closed
6/1/2009 6/15/2009
6/2/2009 6/20/2009
6/3/2009 6/22/2009
6/4/2009 7/5/2009
6/5/2009 7/16/2009
6/6/2009 7/18/2009
6/7/2009 7/21/2009
7/01/2009 7/25/2009
7/03/2009 8/01/2009
8/08/2009 8/10/2009
…and so on
Ex. above, let say Month of June 2009, I want to count only the date that been closed covered on this month.
I am counting separate column but it sucks, it takes a lot of time and cause some mistakes.
Thanks in advance!
Hi Kidd -
Not sure how Col B depends on Col A, and not sure what a right answer is, but if you just want to count the number of months of June in Col B, this works:
=SUMPRODUCT((MONTH(B2:B11)=6)*1)
extend as necessary. Not an array formula. Can not reference all of column B ie B:B
…mrt
This is the very formula that i have been looking for days!
it solve my problem right away.
a lot of thanks for you, GREAT WORK!
how to get sum of two cells while one cell has value and the other one dose not has value in excel 2007
how to get sum of two cells while one cell has value and the other one dose not has value
For ex,(E3/E3*R3*T3/Q3*P3*8/1000000000)+(G3/G3*R3*T3/Q3*P3*8/1000000000)is my formula, I’m not getting values while G3 dose not has any input and the result is ≠DIV/0!
Veeramani -
Well, you’re getting the error because you are dividing by zero in the second part of the sum (G3/G3*R3…)
How do you want to handle that case? What’s your right answer?
It looks to me that (1/R3*T3/Q3…) is an equivalent construction avoiding that division. Similar thought applies for (E3/E3…). They cancel out.
…mrt
Very good stuff here.. Thanks for the post.
This is very close to what I am looking for. Luckily I have a column “F” that lists the days since last login.
I currently use =COUNTIF($F$58:$F$409,”lessthanorequal to 90″)
I do the same for lessthan 30,60,90,and finally greaterthan 90
The problem is that using a pie chart to show percentages does not work with the above very well.
What I really want to capture is
=COUNTIF($F$58:$F$409,”lessthanorequal 30″) (successfully accomplished)
What I can’t figure out and which seems to be very closely discussed here is how to capture the count of instances between the following values:
=COUNTIF($F$58:$F$409,”between 31 and 60″)
=COUNTIF($F$58:$F$409,”between 61 and 90″)
and then end it with
=COUNTIF($F$58:$F$409,”greaterthan 91″) (Successfully accomplished)
I used greaterthan and lessthanorequal in place of trying to use escape characters.
Thanks in advance for any feedback.
Henry — Check out the references to SUMPRODUCT() in earlier comments. It can do what you need easily.
For example, for:
=COUNTIF($F$58:$F$409,”between 31 and 60″)
…you’d use something like:
=SUMPRODUCT(–($F$58:$F$409>30),–($F$58:$F$409<=60))
By the way, those “minus” signs should be double negations. The editor took them out.
The double negations convert True/False boolean values into 1/0 values.
HELP…..
Scenario..Example: I have a start date of 10/08/2009 (cell J2). In Cell AR2, I need to reflect a date that the survey gets sent (which falls within dates on spreadsheet).
The Survey says (in a separate sheet): If a start date falls between 10/4/09 (cell G21) and 10/10/09 (Cell I21), then the Survey gets sent on 10/13/09 (Cell J21). The answer goes in cell G21.
If I have a list of dates, and a spreadsheet to identify when the surveys get sent out, what would the formula be?
Basically, if a date falls in between 2 specific dates, then the answer is…
This is very confusing to me, and I probably made no sense..please help…
Hello,
Can someone help me with the following? I tried some some of the solutions mentioned above but can’t get it working.
Below Product names, date of selling and amount of selling.
Product Date Amount
A 1/1/2009 $100.00
B 1/3/2009 $200.00
C 1/5/2009 $300.00
B 1/9/2009 $400.00
C 2/1/2009 $500.00
B 2/5/2009 $600.00
A 2/8/2009 $700.00
C 3/5/2009 $800.00
I just want to know how many products A, B and C have been sold in January 2009, February 2009 and March 2009
I tried =SUMPRODUCT(‐‐($A$1:$A$9>DATE(2009,1,1)),‐‐($A$1:$A$9<DATE(2006,31,1)),‐‐($B$1:$B$9=”a”),($C$1:$C$9)) but it is not working with the –. If I take them out, I get 0 as result.
Thanks,
Klaas
Thanks,
Klaas
Klass -
As written, you want the sumproduct later than 1/1/2009 and earlier than 1/31/2006.
No such dates.
…mrt
Thank you guys! you really helped me a lot! I used your answers to find out my own formula and i wanted to share it for others like me =)
I wanted to get the total of days between the starting date and the end date of two different vacation periods (in two separate cells) so this is what i got (please let me know if you have a better idea!)
the dates are listed in column C and I enter “1″ for each day taken under the respective employess column
for period 1: =SUMIF($C$8:$C$376,”>=”&D$6,D8:D376)-(SUMIF($C$8:$C$376,”>”&D$7,D8:D376))
for period 2: =SUMIF($C$8:$C$376,”"&D$7,D8:D376))
Thanks a bunch!!!
Hi Everyone,
I have been wrestling with this spreadsheet for weeks. I keep thinking I figured it out and then I find another small mistake. I am trying to find the sum of $ between 2 dates. The spreadsheet started in Nov. 2009 and will continue to be used through the end of 2010. Basically I am calulating the amount of money made per person. I have one table to calcualate a weekly total per person, and one to calulate a monthly total per person.
For example, I would like to calculate amanda’s total between Dec 20 and 26 of 2009 only.
Guest Name C/O Date U/G Total
Macnee, David 28-Nov $40.00
Garcevic, Gojko 30-Nov $260.00
Garcevic, Gojko 1-Dec $80.00
Mincberg, David 21-Dec $179.70
Borkin, Rachel 27-Dec $100.00
Maduro, Fernando 1-Jan $300.00
This is the formula I currently have which gives me the entire total up until Dec 26, 2009. It also adds in the amount of $300 from Jan 2010. =a grand total of 959.70.
=SUMIF(AMANDA!C2:C36,”"&DATE(2010,12,31),AMANDA!F2:F36)
It should equal 559.70. Can someone please tell me what I am doing wrong? Also, how do I format the formuala fo that it works for both 2009 and 2010??
Thank you Thank you Thank you for nay help anyone can give me!! This has been driving me nuts for weeks!!!
Hi!
I am in desperate need of some help. I am tracking the total $ amount of sales per person between two different dates. I am trying to track the sum weekly and monthly. I started the spreadsheet in Novemeber and will continue to use to through the end of 2010. I have 2 problems that I have recently come across using the below formula. The first is that it seems to give me a running total. The second is the amounts that occur in 2010 are messing up my formula.
For example:
Guest Name C/O Date U/G Total
Macnee, David 28-Nov $40.00
Garcevic, Gojko 30-Nov $260.00
Garcevic, Gojko 1-Dec $80.00
Mincberg, David 21-Dec $179.70
Borkin, Rachel 27-Dec $100.00
Maduro, Fernando 1-Jan $300.00
=SUMIF(AMANDA!C2:C36,”"&DATE(2009,12,31),AMANDA!F2:F36)
This formula (which I currently have) gives me the entire total up until Dec 26 AND the $300 amount from Jan 1, 2010. What I would like to do is calculate the total between 12/20 and 12/26 (which is 179.70). I would also like to know how make the formual apply for 2010 amounts as well.
I would appreciate any help I can get as I have been working on this for a month now and keep coming across tiny errors after thinking that I finally got it! Thank you Thank you Thank you to anyone that can help me!!!!
=SUMIF(AMANDA!C2:C36,”"&DATE(2009,12,31),AMANDA!F2:F36)
Sorry! It looks as though the entire formula I have did not post!
=SUMIF(AMANDA!C2:C36,”"&DATE(2009,12,31),AMANDA!F2:F36)
Sorry again!
=SUMIF(AMANDA!C2:C36,”"&DATE(2009,12,31)
,AMANDA!F2:F36)
SUMIF(AMANDA!C2:C36,”>”&DATE(2009,12,31),
AMANDA!F2:F36)
You need to calculate all of the amounts greater than or equal to the start date, and subtract all of the amounts greater than the end date.
=SUMIF(AMANDA!C2:C36,”>=”&DATE(2009,12,12),AMANDA!F2:F36)
-SUMIF(AMANDA!C2:C36,”>”&DATE(2009,12,26),AMANDA!F2:F36)
Hi, IT LOOKS LIKE PEOPLE ARE GETTING LOADS OF HELP FROM THIS SITE SO i THOUGHT i’D GIVE IT A TRY AS I’VE TRIED SO MANY ALTERNATIVES TO THE FORMULA I NEED BUT JUST CAN’T GET IT TO WORK. I WANT TO COUNT HOW MANY TIMES A DATE APPEARS IN A COLUMN BETWEEN TWO DATE RANGES FOR EXAMPLE, HOW MANY TIMES DOES A DATE BETWEEN 01/01/2010 AND 31/03/2010 APPEAR. I DON’T NEED TO CROSS REFERENCE ANY OTHER COLUMNS BUT STILL CAN’T SEEM TO FIGURE IT OUT. CAN YOU HELP ME.
Pleas try this formula to see if it solves your problem. It operates very well in an excel accounting software I have made to automate my office accounts to the balance sheet level.
Note:
(1) $H:$H is the sum_range
(2) $A:$A is the criteria_range
(3) From 01 February to 28 February is the criteria
(4) $D:$D is another criteria_range(in case there is condition 2 to be met)
(5) GOGservice is the second criteria
SUMIFS(SheetName!$H:$H,SheetName!$A:$A,”>=01 February”,SheetName!$A:$A,”<=28 February”,SheetName!$D:$D,”GOGService”).
This is a case of multi-criteria situation. If the condition to be met is one, then the criteria_range comes first and the Sum_range comes last in the formula above.
Good luck
If I understand correctly type this a cell:
=SUM((H6:H65536>=Q10)*(H6:H65536<=R10))
Where Q10 and R10 is a start and a finsh date range and H6:H65535 is the column of dates enquired upon.
Once you have popped the formula in the cell, move away from the cell, then back to it again.
Press F2 then press Ctrl+Shift+Enter to put wavey brackets around it (making it an array).
The result will display as requested, the number of entries recorded with a date between the range specified.
Now my question to all…I need to measure performance
I have a spreadsheet showing columns of start dates and finish dates for many tasks (like calls to a helpdesk, with many calls rec’d each day everyday), when they were opened and when they were closed.
Without using autofilters I need to formulate in another part of the worksheet a running total of how many calls we rec’d in a certain period, how many were opened and closed on the same day, how many took 1 day, how many took 2 days to finish etc.
With this information I then want the spreadsheet to say that in one given period (set by the start date) we had so many tasks recorded, and that we closed a certain percentage within the same day, a percentage with 1 day etc etc.
My previous formula to nicola tells me how many tasks we recorded during a certain time frame, but from there I am stuck
Thanks in advance for the help
Thanks all, for your suggestions.
The winner is:
=SUMPRODUCT((H6:H65535>=Q10)*(H6:H65535<=$R10)*(N6:N65535-H6:H65535=0))
Where H is the list of start dates, N is the list of finish dates, Q is the start-date of the period to query and R is the end-date of the period to query. The result shows those where the start and finish date equals 0, ie on the same day. Changing this value allows you to display where the start and finish day varies
Thanks again all. Excellent blog!
Hi, I’m having trouble counting a range of populated cells where the criteria is set in a different range of cells. It’s like I need the SUMIF formula criteria in COUNTIF:
eg. in the below I want to count the cells that are populated in column B according to criteria in column A. So for “Central” I should get the result to equal 4; or for “Eastern” 1.
Central 5
Central
Central 3
Central 2
Eastern
Eastern 9
Central 8
Help!
Hi
I have been thrown in the deep end with Excel and would really appreciate a little help with a problem that I’ve been faced with.
Basically I have a list of dates in a month (lets say A1 to A10) and what I’m trying to calculate is the average ammount of days between these dates (if that makes sense!!). This will help me too see the average ammount of days between a customers visit to our business.
Thanks
Leigh
Leigh -
Assuming the column is in chronological order (if not, sort it
)
In Column B, B2: =A2-A1, fill down B2:B10
B11: = AVERAGE(B2:B10)
Good Luck,
…mrt
Chrisso -
=SUMPRODUCT(–(A1:A7=”Eastern”),–(B1:B7>0))
=SUMPRODUCT(–(A1:A7=”Central”),–(B1:B7>0))
…mrt
I would like to have a formula for the following:
cell a1 = date (i.e. 1 Mar 10)
in cell a2 I would like it to calculate a countdown of 60 (days) from that date so that it would show 56 if the current day was 4 Mar 10. I would also like the cell to be green if greater than 30 and yellow if less than 30. At 0 and less I would like the cell to be red.
Any help would be greatly appreciated by a non-excell type guy. Thanks!!!
Make that “non-exel”
Hi Robert -
A2: = A1 + 60 - TODAY()
Number Format as General
Conditional Format A2 (Format/Conditional Formatting)
Condition 1: Cell Value is…Greater Than…30 (pick green font or pattern)
Condition 2: Cell Value is…between…0…and…30 (pick yellow font or pattern)
Condition 3: Cell value is…less than or equal to…0 (pick red font or pattern)
Note that you didn’t say what to do if Cell Value = 30
Adjust the above as desired. Good Luck.
…mrt
Thanks Michael, I appreciate it. Good catch on the 30, I’ll do an =/> 30 for it.
BTW Michael I had drill this weekend so I haven’t had time to get back on here and check for comments, just in case you’re wondering why it took me so long to reply back. We really do more before 9 am than most people do all day, but not always.
I would like to have a formula to return a value on a new worksheet for the following scenario:
1) find a date match in column B - the example below is 11/28/2009
2) compare the dates in column A for the greater of these based on the matched column B and return the amount from column D
Non-Excel example:
If(B2:B24=Date(2009/28/11) then compare dates in column A for the greatest date and return that unit1 from column D.
In this example the return result would be .0146
A B C D
Posting Date Delivery Date Amount Unit1
12/17/2009 11/28/2009 .04 .0157
02/04/2010 11/28/2009 .00 .0146
Any assistance would be greatly appreciated.
Funinsum:
What’s the sorted condition of Columns A and B?
And if unsorted, can they be sorted?
…mrt
Michael,
Columns A and B are sorted as follows:
Column B sort by Delivery Date in order oldest to newest
Column A sort by Invoice Posting in order oldest to newest
They can be sorted in any fashion to accomplish the end result.
Thanks
Funinsum
I would like to have a formula that will determine the average of a summed column divided by the ISO Week #
For example - a cell (Q57) has this formula =sum(q4:q56) and it returns $5,000.00 I want a formula for cell R57 that will average that sum by the ISO Week#, so if it is week # 4, I want to see an answer of $5,000.00/4 which equals $1,250.00 - This would be a self adjusting formula determined by the current week # - I am looking for the average year to date income per week
Funinsum -
That being the case, we only need to find the last 11/28 to be coincident with the greatest date in Column A.
MATCH(DATE(2009,11,28),B:B,1) will do that. The 1 is important. It says to find the greatest or equal to 11/28/2009. This returns 3.
We then need to index Column D three rows.
So, in a convenient cell not in Columns B or D, say E1.
E1: = INDEX(D:D,MATCH(DATE(2009,11,28),B:B,1))
Returns 0.0416. Make sure your dates are numbers formatted as dates, and not text looking like dates.
…mrt
George -
Check this page http://www.cpearson.com/excel/WeekNumbers.aspx
for the UDF to compute the ISO week number. It’s near the bottom.
…mrt
Hi Michael,
I can see the value of this site! Let me first state - I am a word gal, not a numbers or formula’s gal…with that said, I’ve tried to read through the posts, but I remain confused. I hope you can help. I hope I can articulate this.
I want to find a way to document start and end points between several dates and represent by color “chart” (think gantt chart) on the same row as the data. However, I can’t have a separate chart and line it up. The spreadsheet data is so long, I can not export to a separate chart and import (and I lack the ability to do this with up to 6 start/end dates and visually make it work). Essentially, there is a start and end point to a project, and I need to show the start and end dates for 1-4 events that occur throughout between the start and end dates of the projects in order to compare the volume and impact of multiple projects on a monthly and quarterly basis for up to three years. Thus needing to see this visually, but still read all the data for each project. I’d cut and paste the row of data as an example, but it doesn’t make any sense when I do.
Of course, got the project, there’s a fire under it…and it would take me forever to manually create this!
Help!
Thanks - JK
Hi Joyln -
This is probably a place where a picture = 1 kiloword. Here’s a website that has an example at the bottom of what I think you want:
http://www.juiceanalytics.com/writing/more-on-excel-in-cell-graphing/
Quoting: “Some folks raised the interesting (some would say perverse
) idea of using this technique to create Gantt charts. Here’s an example. I’m using the fact that the width of a space character is exactly 1.5x the width of a bar character in Arial to make this work.”
A spreadsheet is provided. It’s in-cell graphing.
Other place to look:
http://peltiertech.com/Excel/Charts/GanttChart.html
Good luck,
…mrt
Hi All,
I have a problem at hand… Left hand, ‘rows’ there are different names. On top, ‘column’ there are dates 1st to 30th. In body there are 1, 2 & 3s
I need for a specified name & for a specified date range count of 1’s. Similarly 2’s & 3’s.
Pivot is very difficult with 30 different columns.
Countif and Sumif falls short by one dimention. I dont know how to do sumproduct.
I might be blinded with limited thinking here but any help would be life saving…
Thanks in advance.
Looking to sum values, beginning today and going back 90 days. Try to create a spreadsheet for folks not too familiar with Excel, so I need to create a function that minimizes data entry. Can I use the TODAY function to establish the end dat for the 90 day period?
Kevin -
Yep. TODAY()-90 at one end, TODAY() at the other.
…mrt
Thanks for the post… very helpful. I had to modify this a bit, but it got me started.
I actually had three criteria to meet. I needed to sum the number of maintenance hours worked for a specific property and only between two dates. To do this I used the SUMIFS() formula.
my formula looked like this…
=SUMIFS(E3:E166,B3:B166,H4,A3:A166,”<”&J2)-SUMIFS(E3:E166,B3:B166,H4,A3:A166,”<=”&I2)
in english…
=SUMIFS(hours_column,property_column,specified_property,date_column,”<”&beginning_date) -SUMIFS(hours_column,property_column,specified_property,date_column,”<=”&ending_date)
My Table (office 2007) contains dates, category, Checking acct transactions, and Savings account Transactions.
My summary is by date range and by category, and I tally transaction in multiple accounts.
Here is what I did:
=SUMPRODUCT(
–(t_Expenses[Date]>=$E$3)*
–(t_Expenses[Date]<=$G$3)*
–(t_Expenses[Category]=D4)*
t_Expenses[[Checking]:[Savings]])
where E3 is the start date
G3 is the end date
D4 (and others) are the categories
I like this for a few reasons: it works, unitary operator plus only 1 parameter in the SUMPRODUCT is efficient, I can choose the date ranges (actually I use a data validation drop down) and dont need the ugly quadruple formula: DATE(y,m,d), I can have as many accounts as I want (inserted between Checking and Savings columns), and one formula for each category. Oh yeah, adding more criteria is easy: * = OR and + = AND.
Jay:
If you’re going to multiply the conditions against each other like that, there’s really no need for the double-negation. The very fact you’re doing the multiplication in the first place will make EXCEL convert the Boolean values into numeric values.
For example, all three of these operations convert Boolean into numeric values of 1 and 0:
–(A1<B1)
1*(A1<B1)
0+(A1<B1)
Since double-negation confuddles some people, they prefer the last two over the first.
The conversion of the Boolean expression is really only needed if it is a separate SUMPRODUCT() parameter. And, actually, there is an advantage to doing each Boolean argument as a separate parameter — you can avoid a #VALUE! error if something in your final array of numbers isn’t numeric (e.g. a header line). This can allow you to use a header and trailer line as part of your array processing, so it’s easy to insert new data in between and have all the formulas automatically adjust to the new range size.
Column A Column B Column C Column D Column E
amount date date date
Water rates 30.00 01/09/10 01/10/10 01/11/10
Electric 60.00 01/09/10 01/10/10 01/11/10
Phone 20.00 15/09/10 15/10/10 15/11/10
I’m using a sumif function to say if the date is equal to any of the dates in columns C to E then return the amount in column B.
It works fine if the date falls into the first column of dates but after that it sums column B and then column c’s date even though I have used $b1:$b3 to specify that I only want it to sum column B figures. Can you help?
My spreadsheet totals invoice sales from 1/1/10 - 12/31/10. I need a formula to sum only sales from 1/1/10 to 6/30/10. Then another formula to capture all sales on or after 7/1/10.
b2:b95 (date of job)
d2:b95 (amount of job)
b97 - formula entered: =sumif(B2:B95, “=7/1/10″, D2:D95)
cells in b2:b95 are formatted as date 7/1/10 so user only enters 7/1 and it returns 7/1/10.
Not sure why this is not working … any suggestons?
@Shell,
Try these two formulas…
On or before 6/30/2010
———————————
=SUMIF(B2:B95,”=”&DATE(2010,7,1),D2:D95)
Note: As written, these two formulas will not confine themselves to the year 2010, so if other years are present in the specified range, these formula would have to be modified.
@Shell,
Hmm! Something got completely fouled up in my previous answer to your question. Let me try again.
Try these two formulas…
On or before 6/30/2010
================================
=SUMIF(B2:B95,”=”&DATE(2010,7,1),D2:D95)
Note: As written, these two formulas will not confine themselves to the year 2010, so if other years are present in the specified range, these formula would have to be modified.
I know my example was not showing the less than symbol.
=sumif(B2:B95, “<=7/1/10″, D2:D95) Note I had a less than symbol in my formula infront of the =7/1/10.
I’ll try yours again and see. Thanks,
It did it again! I think I see the problem. My first formula has a less that or equal sign in it and my second formula has a greater than or equal sign in it… the comment processor is using the greater than and less than symbols as HTML tags. Let me try again, but this time using the VB Code tags (even though this isn’t VB code) and see if that will work…
Try these two formulas…
On or before 6/30/2010
================================
On or before 6/30/2010
================================
Note: As written, these two formulas will not confine themselves to the year 2010, so if other years are present in the specified range, these formula would have to be modified.
I am trying to create a formula in Excel 2003 that will add numbers that occur between the current date and 6 months ago.
A B
9/13/2009 8
9/26/2009 8
11/14/2009 3
3/10/2010 8
3/22/2010 2
4/11/2010 8
5/24/2010 4
6/22/2010 0
8/4/2010 8
I have the cell L2 to indicate todays date when the spreadsheet opens and cell M2 to indicate the calculated date 6 months ago. I need a formula that will add the values in B that fall between the L2 and M2 dates.
I know that this is the longest running active Excel blog thread ever. Is there any way to find out if it is the longest blog thread ever?
There has to be enough SUMPRODUCT information here to write a book(or at least a chapter)! Maybe this thread will morph into a substitute for the Excel newsgroups
Hello All
I cannot use the drawing toolbar as it comes with grey colours and I do not how to change them to make them usable.
I have had some help in the past and I am grateful for the solution.
Thanks
Eddie
Or morph into one of those ‘competition’ threads where the last one to post wins!
Hello,
I have a large list of customers that includes the following information: customer number, name, active date, and inactive date. I need the number of customers active say between 08/01/10 and 08/31/10 displayed in a list by the customers number and name in a specified column(s). Any help is greatly appreciated. Thanks.