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.
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.
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.
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
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.