Conditional Sum excel 2003

Microsoft Excel 2003 (full product)
February 12, 2010 at 11:37:18
Specs: Excel 2003
I am trying to sum order $'s by date using the below formula, I only want a total of June 2010 dates. My formula is suming all June dates regardless of whether they are 2009, 2010, or 2011.

{=SUM(IF('02-11-10 Detail'!$G$2:$G$7009>"05/31/2010",IF('02-11-10 Detail'!$G$2:$G$7009<"07/01/2010",'02-11-10 Detail'!$I$2:$I$7009,0),0))}

Dates are stored in Column G, and order $'s are stored in column I.

The formula is working except for pulling June 2009 and June 2011 $'s

See More: Conditional Sum excel 2003

February 12, 2010 at 12:21:35

An easier way to sum amounts for dates ranges is as follows:
Create a small date range table:

	A		B	 	C
1	Start		End 	 	$
2	01/Jan/08	31/Dec/08	
3	01/Jan/09	31/Dec/09	
4	01/Jan/10	31/Dec/10	
In this example dates were in column G from rows 3 to 5000
and $ amounts were in column I

Put this formula in cell C2:


Note the $ signs (required for correctly extending the formula by dragging it).
Drag the formula to extend it to the other two date ranges

Here is what I got, and the total in column H was the same as the total of the three date ranges shown.

	A		B	 	C
1	Start		End 	 	$
2	01/Jan/08	31/Dec/08	 $235,476.89 
3	01/Jan/09	31/Dec/09	 $241,442.37 
4	01/Jan/10	31/Dec/10	 $29,296.43 

This formula multiplies three things together - note the * signs.
The first thing it does is to look at each cell in the range $G$3:$G$5000 and compares it to the value in cell A2
If the date in any cell in column G is equal to or greater than the date in A2 it returns a value of 1, but if not in this date range it returns a value of zero.

As you know anything x 0 is zero.
So all cells that are less than the date in A2 will be zero

The next part does the same sort of calculation but this time it returns 1 only if the cell has a date less than or equal to the date in Cell B2

Now only cells with a date in the range A2 to B2 (01/Jan/08 to 31/Dec/08) return 1
All other cells return zero.

Finally it takes the value in the cells in column I
It sums the values in all the cells in all the rows 3 to 5000 in column I, after multiplying them by what it has so far which is 1 or zero for each row
So each value in column I is multiplied by 1 or 0 before being summed.

Adjust date ranges to suit.
For months the EOMONTH function is useful as it returns the last day of a month irrespective of the number of days in a month
In the example above =EOMONTH(A4,0) will return 31/Jan/2010, and it works for leap years.

Hopefully slightly clearer than mud.


Report •

February 12, 2010 at 12:47:36

Looking at the formula you posted, it is likely not working as expected because you are using string text for comparisons "05/31/2010", rather than real dates. Try DATEVALUE("31-May-2010")
or for more flexibility put you dates in separate cells and reference them in the formula:
{=SUM(IF('02-11-10 Detail'!$G$2:$G$7009>=A2,IF('02-11-10 Detail'!$G$2:$G$7009<=B2,'02-11-10 Detail'!$I$2:$I$7009,0),0))}
where A2 and B2 are the start and end dates. As A2 and B2 are already recognized by Excel as dates, you don't need to use DATEVALUE around them.
Note that DATEVALUE can be picky about the date format in the string, so again, external references to dates is easier.


Report •

February 12, 2010 at 12:56:00
On response #2 I figured it had something to do with the way the dates were stored, but then I thought if that is the case, why was it pulling any info at all? But it definately was pulling June $'s, in the right range, just not the correct year.

I will try what you suggested above on Monday.

Thank you.

Report •

Related Solutions

February 15, 2010 at 05:26:16
This formula finally ended up working

{=SUM(IF('02-14-10 Date PSC '!$G$2:$G$7000>=$T$8,IF('02-14-10 Date PSC '!$G$2:$G$7000<=$U$8,'02-14-10 Date PSC '!$K$2:$K$7000,0),0))}

I stored 6/1/2010 in T8, and 6/30/2010 in U8.

The problem was in how I had the date stored in the worksheet.

Thank you

Report •

February 15, 2010 at 05:58:07

Glad its working and thanks for posting your solution.


Report •

Ask Question