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.
Maurice:
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!
31 March 2004, 11:41 pmEric Hanley:
This is just what I was looking for; an exact solution for the exact problem I have.
8 May 2004, 8:16 pmThe 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?
Dick:
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.
9 May 2004, 5:16 pmWill:
Thanks a million - saved me loads of head-scratching!
11 May 2004, 5:27 amHaydn:
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
29 June 2004, 3:49 amJeff:
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.
13 July 2004, 9:24 amDaveC:
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!
6 August 2004, 3:17 amNigel:
Very nice. I have been working around this issue for yonks. Can’t believe it was that simple!
3 September 2004, 3:27 amBill:
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.
4 November 2004, 10:39 amKatie:
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!
10 December 2004, 2:59 pmFrank Kabel:
Hi
10 December 2004, 5:38 pmuse sUMPRODUCT. See:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Erick:
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,”
2 March 2005, 9:40 amErick:
=SUMIF(TrafficDataBase!A10:A550,”
2 March 2005, 9:41 amDonna:
Can’t thank you enough for this answer and for this site!
9 May 2005, 10:15 amJeff:
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
20 May 2005, 4:29 pmJeff:
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
20 May 2005, 4:53 pmStephen:
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,”
14 June 2005, 6:09 amJay:
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.
23 June 2005, 7:35 amRob van Gelder:
Jay,
Sure can!
=SUMIF(A1:L1, “
24 June 2005, 3:06 amRob van Gelder:
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
24 June 2005, 3:10 amNik:
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.
7 July 2005, 2:50 amLance G.:
Can you do sumif with 2 criteria?
19 August 2005, 8:39 amFor 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?
Rob van Gelder:
Lance,
Check out this example which uses SUMPRODUCT.
Multicolumn Sum with Wildcard
Cheers,
20 August 2005, 12:50 amRob
Chuck:
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.
17 January 2006, 11:03 pmChris Strazzeri:
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,
24 January 2006, 10:46 pmChris
Chris Strazzeri:
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,
24 January 2006, 11:00 pmChris
Chris Strazzeri:
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
25 January 2006, 7:21 amJamie Pryer:
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.
9 February 2006, 6:51 amI 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?
Mark:
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.
15 February 2006, 9:17 pmJonny:
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
15 March 2006, 10:48 amJonny
Jonny:
Please ignore my previous post, no 30. Issue resolved.
Thank you
Jonny
15 March 2006, 11:14 amRubel:
In sheet Greater Than and Less Than Operator is not working….Pls advise me
26 April 2006, 12:48 amMike:
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,
26 April 2006, 4:45 pmMike
Mark the Firefighter:
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,
7 May 2006, 4:37 amMark
Lori:
Mark - Try this in the ‘ThisWorkbook’ module:
Private Sub Workbook_Open()
9 May 2006, 11:12 amApplication.Goto “index(c1,match(today(),c1,0))”, True
End Sub
Dylan:
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,
15 May 2006, 5:31 pmDylan:
it may have cut off the last bit… SUMIF(A1:A100,
15 May 2006, 5:33 pmJOHN:
Thank you for providing such a help to me, I appreciate it , and this formula works perfectly
20 May 2006, 9:17 amfrank:
Omg did that help me, thanx very much
24 May 2006, 12:20 pmHeather:
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
24 May 2006, 1:20 pmMary:
Park’s question on post 29 is exactly what I’m trying to do, but
25 May 2006, 2:31 pmI don’t see an answer. Can you help?
RJS:
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
26 May 2006, 2:15 pmJake Marx:
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,
26 May 2006, 2:21 pmJake
RJS:
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
26 May 2006, 2:23 pmRJS:
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″”
26 May 2006, 2:37 pmTraderdeath:
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,”
31 May 2006, 5:45 pmTraderdeath:
More post. Didn’t realize. sorry.
=SUMIF(DAILY!B:B,”
31 May 2006, 5:47 pmJake Marx:
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
1 June 2006, 3:23 pmruike:
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
3 June 2006, 5:21 pmJake Marx:
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,
5 June 2006, 2:11 pmJake
Mike:
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.
27 June 2006, 11:08 pmMary:
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
28 June 2006, 12:06 pmJonathan:
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
29 June 2006, 2:20 pmJonathan:
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.
29 June 2006, 2:41 pmKylie Middleton:
Thanks a bunch - just the solution I was looking for!! So Happy, Oh so Happy!
29 June 2006, 7:45 pmTammie:
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?
3 July 2006, 12:54 pmDick Kusleika:
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.
5 July 2006, 8:04 amTammie:
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?
6 July 2006, 5:36 amDick Kusleika:
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.
6 July 2006, 6:02 amJake Marx:
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
6 July 2006, 3:21 pmTammie:
thank you for your help. It worked.
13 July 2006, 6:33 amAndrew:
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,”
13 July 2006, 11:37 amAshley:
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
15 July 2006, 2:19 amTammie Thomas:
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.
25 July 2006, 7:24 amT4noc4po:
Guys
YOU SAVE MY LIFE!!! With SUMPRODUCT i found what i wanted.
29 July 2006, 2:15 pmNow, the two negatives (”–”) what’s that mean??
Anastasia:
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…
19 December 2006, 4:33 amahmed:
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.
24 December 2006, 3:26 ampls give me the formula
regards
alan:
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?
5 January 2007, 8:58 amRob van Gelder:
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
5 January 2007, 7:53 pmEl:
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.
18 January 2007, 3:15 pmEl:
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
21 January 2007, 10:16 amActivity 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
Rob van Gelder:
El: please see blog posted 21-Jan-2007 for a solution
21 January 2007, 2:31 pmEl:
That is exactly what I was looking for. You made it so easy. Many thanks Rob.
21 January 2007, 3:23 pmChristy:
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
25 January 2007, 12:14 pmor 8/1/06-12/31/06=2006
Christy:
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
25 January 2007, 12:16 pm9/1/05-12/31/05 2005
or
8/1/06-12/31/06=2006
Matthew:
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.
25 January 2007, 1:31 pmDavid:
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!
7 February 2007, 5:26 amMartin:
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.
7 February 2007, 1:32 pmSascha:
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;”
8 February 2007, 4:39 amSascha:
the formula i’ve entered is:
8 February 2007, 4:41 amsumif(B2:B9;”
Martin:
Crystal ball gazing a little but what’s wrong with =sumif(B2:B9,D17,C2:C9)
8 February 2007, 1:43 pmDick Kusleika:
Sascha probably wants a less than or greater than (which is why his comment is being truncated). For that, try
=sumif(B2:B9,"<"&D17,C2:C9)Here’s how I typed that in (with extra spaces):
< code > =sumif(B2:B9, & quot ; & lt ; & quot ; & amp ; D17,C2:C9) < / code >
8 February 2007, 2:25 pmZonaCat:
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
8 February 2007, 10:03 pmMartin:
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
=SUMIF(A:A, & quot ; & lt ; & quot ; & amp ; Today(),B:B)Sum to Today
=SUMIF(A:A, & quot ; & lt ; & quot ; & amp ; Today()-1,B:B)Hope this helps.
11 February 2007, 7:38 pmMartin:
- 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
=SUMIF(A:A,"<"&Today(),B:B)Sum to Today
=SUMIF(A:A,"<"&Today()-1,B:B)Hope this helps.
11 February 2007, 7:44 pmMartin:
Oh f… after all that I got the formulas round the wrong way, Yesterday = Today()-1
11 February 2007, 7:46 pmJosh:
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.
12 February 2007, 9:24 amJOSH:
POSTAL CODE RATE CODE NOMINAL
12 February 2007, 1:34 pmA0A-A0O 6 AOA
josh:
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.
12 February 2007, 1:36 pmDick Kusleika:
Josh: We need more of the list of postal codes, like five or ten of them. You want a lookup-type formula?
12 February 2007, 4:33 pmJosh:
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.
13 February 2007, 6:53 amthanks!
Martin:
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:
=VLOOKUP(LookupValue,A1:B26,2,TRUE)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.
13 February 2007, 2:23 pmJosh:
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.
14 February 2007, 1:35 pmthanks
Stuart:
=SUMIF(Data!$H:$H,”
16 February 2007, 5:13 amDoug:
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.
22 February 2007, 8:45 pmMartin:
Doug, refer no. 85. Use same method but with Today()-90
25 February 2007, 3:31 pmChristian:
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
2 March 2007, 12:12 amChristian:
The formating didnt quite go through on the last post. Using periods for spaces.
2 March 2007, 12:23 am…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
MaryU:
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!
6 March 2007, 8:54 amAdam:
Can anyone explain why this formula is not working?
=COUNTIF(H:H,AND(”>=”&DATE(2007,2,1)=TRUE,”
12 March 2007, 4:06 pmdesmond:
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
28 June 2007, 1:00 amJon Peltier:
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).
28 June 2007, 7:43 amDesmond:
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
3 July 2007, 2:59 amif 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
Hui..:
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”))
3 July 2007, 8:56 amNRB:
Another problem slightly different to but related to many I’ve seen here.
5 July 2007, 7:46 amI 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.
Rod:
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?
19 July 2007, 3:35 pmRod:
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?
19 July 2007, 3:39 pmHui...:
+sumproduct(1*(Col1>=Date1)*(Col1
20 July 2007, 1:51 amHui...:
Rod
20 July 2007, 1:54 amDon’t know what happened with the last post
Try
Q1 +sumproduct(1*(Col1>=Date1)*(Col1=Date1)*(Col1
Hui...:
Rod
20 July 2007, 1:59 amDon’t know what happened with the last post
Try
Q1 +sumproduct(1*(Col1>=Date1)*(Col1=Date1)*(Col1
frank:
=SUMPRODUCT((date>=A2)*(date
20 July 2007, 11:24 amfrank:
=SUMPRODUCT((date>=A2)*(date”
20 July 2007, 11:25 amfrank:
=SUMPRODUCT((date>=A2)*(date smaller or equal to A6)*amount)
(used text instead of the necessary escape characters)
20 July 2007, 11:38 amDick Kusleika:
This is a test ( < )
24 July 2007, 2:22 pmrichard:
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.
23 August 2007, 5:56 amGary Makowski:
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!
11 September 2007, 11:00 pma 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
Gary Makowski:
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:
11 September 2007, 11:03 pmabc 2005 150
abc 2006 100
abc 2007 0
Dick Kusleika:
=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.
14 September 2007, 10:36 amGary Makowski:
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!
26 September 2007, 1:46 pmSan:
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
28 September 2007, 4:50 am1400
700 1500
900
800 2900
500
600
matt:
Refering to Jakes answer on May 26, 2006
1 October 2007, 10:33 amWhat if the dates were between b1-a3, aud/usd were in a2-a3, and the values were
between b2-d2
matt:
Sorry I messed up***
1 October 2007, 10:34 amRefering 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
Tammy:
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
3 October 2007, 12:42 am1/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
David:
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!
7 October 2007, 1:29 amDick:
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.
27 November 2007, 11:01 pmThanks
Pete:
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
5 December 2007, 9:08 pmPete:
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?
5 December 2007, 9:37 pmDick:
Pete,
8 December 2007, 2:12 pmoh 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.
Pete:
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.
10 December 2007, 3:37 pmScott Ellis:
Great forum. This has been very helpful. The formula =SUMIF(A1:A9,”
10 December 2007, 4:11 pmScott Ellis:
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…
10 December 2007, 4:24 pmHimanshu:
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
27 December 2007, 4:38 am1 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
laraine:
you are sure great, have used the sumif method for my excel problem, thanks a bunch!
5 February 2008, 7:26 pmCamille:
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
14 February 2008, 3:20 pmBrian:
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,
18 February 2008, 10:17 amBrent:
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.
21 February 2008, 11:46 amDanny W:
What is excel formula for counting the number of cells containing data in between two dates?
26 February 2008, 2:29 pmLeonel Quezada:
Try this array formula:
=SUM((mydates>=date1)*(mydates=date1),(mydates
28 February 2008, 3:54 pmLeonel Quezada:
Sorry.
Try this array formula:
=SUM((mydates>=date1)*(mydates=date1))
Regards.
28 February 2008, 3:58 pmAaron:
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?
3 March 2008, 10:46 amBrett:
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.
17 March 2008, 4:55 pmLuis:
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,”
26 March 2008, 4:54 amLuis:
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?
26 March 2008, 8:58 amGary Makowski:
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!
16 April 2008, 5:59 pmElias:
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
17 April 2008, 11:01 amElias:
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.
17 April 2008, 11:05 amGary Makowski:
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
17 April 2008, 2:53 pmGary Makowski:
PS: I successfully adjusted it to so as to find the last date of any order.
17 April 2008, 3:01 pmElias:
I’m glad you got it.
18 April 2008, 6:58 amGary Makowski:
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
19 April 2008, 1:01 amElias:
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
20 April 2008, 11:04 amAdrian:
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
22 April 2008, 9:29 amEllie P:
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:
25 April 2008, 2:36 pm=SUMIF(’\\rfdpfs090\pmiswap\Common\Rail Traces\Alfalfa - Fort Worth\[_2008 Alfalfa - Fort Worth Turn Time.xls]2008′!$B$4:$B$105,”
Ellie P:
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,”
25 April 2008, 2:37 pmGary Makowski:
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
28 April 2008, 10:44 amSam:
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?
30 April 2008, 1:45 pm1/5/07 - 5
1/5/07 - 3
1/6/07 - 1
Ed:
I’m using MS Excel 2002, why doesn’t this work?
4 May 2008, 10:37 pm=COUNTIF(G2:G139,””&DATE(2006,12,31))
Geko:
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.
22 May 2008, 8:02 amRob van Gelder:
Geko:
22 May 2008, 11:28 pmAssuming the date is in cell A1
=INT((MONTH(A1) - 1) / 3)
Elias:
Try this,
=LOOKUP(MONTH(A1),{1,4,7,10},{”Q1″,”Q2″,”Q3″,”Q4″})
Regards
23 May 2008, 6:59 pmElias:
Other way,
=”Q”&MATCH(MONTH(A1),{1,4,7,10})
23 May 2008, 7:01 pmGeko:
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!
27 May 2008, 9:00 amDCardno:
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.
27 May 2008, 11:52 amMark:
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.
10 June 2008, 2:55 pmJack:
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
17 June 2008, 3:28 pmJack
Leigh:
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?
17 June 2008, 8:39 pmGeko:
Hi,
16 July 2008, 7:03 amThis 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
RICKEY:
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
24 July 2008, 1:31 pmmark:
rickey,
just replace Date(x,y,z,) with a cell reference.
Geko,
26 July 2008, 10:38 amLook into conditional formatting.
Alvin:
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
28 July 2008, 12:36 amAlvin:
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″),
28 July 2008, 1:14 amIF($A$2:$W$20
Geko:
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
31 July 2008, 5:33 amAl:
Hi,
26 August 2008, 2:49 pmThis 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