Easy Excel formula question.
I want to sum a column of hours ( expressed as a single digit like 8 )
Column A has a date, Column B has a number.
I want to sum all the numbers in column B that are in September.
Cell C1 would have the starting range, 9/1/08 and cell D1 would have the ending range, 9/30/08. What is the fomula in cell E1 that would acheive this.
Thanks for your help.
Bob Z
You can do it by converting the date with the DATEVALUE command.
=DATEVALUE(“9/1/2008”) (put this in C1)
gives 39692. This is the number of days that have elapsed since January 1, 1900.
similarly,
=DATEVALUE(“9/30/2008”) (put this in D1)
gives
From there, I -think- you have to use a lookup or IF command, but there’s a start.
Also, you may be able to simply sort A and B in ascending order, and use the sum(cell:cell) command to find the answer manually.
Okay, I’ve got it. Column E would have:
=IF(AND(C1-1<A1, D1+1>A1), B1, 1)
the - 1 and + 1 are to account for the less than/equal to condition.
you can also do
=IF(AND($C$1-1<A1, $D$1+1>A1), B1, 1)
if you just want to refer to the two cells and not make C and D full columns.
Then, simply take the sum of column E. If you have 10 data points, the command would be:
=SUM(E1:E10)
On further review, it seems like you don’t even have to use the DATEVALUE command, the date in the proper format should suffice.
Am I misunderstanding the question or can’t you just use the SUMIF function? Here’s what I did on a quick spreadsheet:
Column A has the dates.
Column B has the hours that you want to sum up.
Let’s say we’ve got 10 rows of data.
To get the total hours in B corresponding to dates in A that are in September, the formula is:
=sum(b1:b10)-sumif(a1:a10,"<09/01/08",b1:b10)-sumif(a1:a10,">09/30/08",b1:b10)
I wasn’t able to use a single sumif with an “AND” in the criteria to check that a date was in the correct range so this is a simple workaround - you total up all the hours, subtract the hours worked before September and then subtract the hours worked after September.
Give that a try.
Does your A column have some dates that aren’t within the period?
If so, a SUMIFS statement sums only those values associated within C1 and D1
The statement is shown below for a list which goes from A2 to B100
=SUMIFS(B2:B100,A2:A100,">="&C1,A2:A27,"<="&D1)
For my example it returns the number 11 after summing only those values (all ones for clarity) in column B from Sep 20 to Sep 30.
Values in A2:B27
9/20/2008 1
9/21/2008 1
9/22/2008 1
9/23/2008 1
9/24/2008 1
9/25/2008 1
9/26/2008 1
9/27/2008 1
9/28/2008 1
9/29/2008 1
9/30/2008 1
10/1/2008 1
10/2/2008 1
10/3/2008 1
10/4/2008 1
10/5/2008 1
10/6/2008 1
10/7/2008 1
10/8/2008 1
10/9/2008 1
10/10/2008 1
10/11/2008 1
10/12/2008 1
10/13/2008 1
10/14/2008 1
10/15/2008 1
On Edit After seeing Valgard’s post I’m concerned that my SUMIFS may be only available in Excel 2007. SUMIF is available in earlier versions and you may have to use Valgard’s method if you dont have 2007
Thanks for your help Santo, I’ll try it.
I was wondering if there was a way to do it with a sumif comand, where the sumif formula would work on the specified date range. Then I would update the cells for any particular date range needed.
No problem, but the SUMIF command is more eloquent. Learn a new command every day, it seems.
Also, I didn’t know you could use the >= combo in Excel, so you wouldn’t need the +1 and -1 parts of my code.
I have Excel 2003 here at work and there’s no SUMIFS function. Pity, it looks very slick!
=sumproduct((month(a2:a14)=9)*(b2:b14))
You could add a Month column (+MONTH(cell)) and then build a pivot table with Row Field of Month and a Data Drop of Column B. Then click Field Settings on the Pivot table to give you a Sum not a count of the data
Though Bean Counter’s is elegant.
It has COUNTIFS and AVERAGEIFS cousins too.
I use them a lot with as many as 7 or 8 criteria.
Here’s a little article I just found where the author uses an example that fits robz’s problem.
Boolean-wise its similar to using an AND extension in a criteria.
It would be a killer if they could incorporate an OR feature into similar functions.
Grr, this is exactly what I came in here to type. There’s no need for columns C and D with this formula.
New Excel question:
Say I have a column of numbers. Test results from a gradebook (0-100, some numbers duplicated).
I want to show the class the distribution (like a bell-curve).
Simplest method?
You could do a simple bar graph, the only problem would be that an 85 would be a different bar on the chart than 84 and probably would not show the bell you are looking for, even though they are still in the “B” letter grade range.
Say you wanted to show the distribution of the letter grades (A+'s, A’s, A-'s, etc.) you could first create a table and have it count the number of instances of each grade and then graph that.
Start with the A+'s, and use the following formula:
=COUNTIF(score range, “>=97”)
(or whatever the bottom range of your A+'s are.)
Then below that, count the regular plain A’s:
=COUNTIF(score range, “>=93”)-A2
(assuming you counted the A+'s in cell A2)
The A-'s would then count everything equal to and above, say, 91 with it then subtracting the SUM(A2…A3) or whatever the range is for the A+'s and A’s.
Continue through your letter grades and then graph the result.
Very similarly, if you don’t want to clump results into letter grades or bar graphs, you can use a scatterplot with a trendline. The method is pretty much the same as Bean Counter’s, except with more precision in the COUNTIF formula and charting.
See example here:
http://drop.io/gradesdistribution