Microsoft Excel 2003 (full product)

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

Hi, 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/10In this example dates were in column G from rows 3 to 5000

and $ amounts were in column IPut this formula in cell C2:

=SUMPRODUCT(($G$3:$G$5000>=A2)*($G$3:$G$5000<=B2)*($I$3:$I$5000))

Note the $ signs (required for correctly extending the formula by dragging it).

Drag the formula to extend it to the other two date rangesHere 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.43This 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 zeroThe 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,aftermultiplying 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.

Regards

Hi, 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.Regards

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.

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

Hi, Glad its working and thanks for posting your solution.

Regards

Ask Your Question

Weekly Poll

How long do you think until flying cars or taxis are common?

Discuss in The Lounge

Poll History