Excel sum formula with dates

Microsoft Windows vista home premium 64-...
March 5, 2010 at 06:42:30
Specs: Windows Vista, 1014MB
Hi
Could you please let me know how can I add up the totals if when one the column contains dates? SUM( ( Sheet1!A2:A7 = "in" ) * ( Sheet1!B2:B7 ="Jan-10") * Sheet1!C2:C7 ) it return 0... ideally would be to get the total from c column if A column = in, and B column = Jan feb and march. is it possible?

See More: Excel sum formula with dates

Report •

#1
March 5, 2010 at 09:38:38
B2:B7 ="Jan-10"

Is it
January 10th 2010 or
January 2010?

MIKE

http://www.skeptic.com/


Report •

#2
March 5, 2010 at 09:50:02
Assuming it is January 10th 2010, try this:

=SUM( ( A2:A7 = "in" ) * ( B2:B7 = DATEVALUE("Jan-10")) * C2:C7 )

Remember it's an array formula, use CTRL - SHIFT - ENTER

MIKE

http://www.skeptic.com/


Report •

#3
March 5, 2010 at 10:29:43
hello again:) the actual dates are Jan-10 Feb-10 etc but they ve been put onto spreadsheet as 01/01/10 01/02/10 but because the column is formated as Date(mmm-yy) it shows as Jan-10 etc. also if i wanted to do that quarterly (jan feb and march, then apr may june ...) would that be possible?or better add the totals separetly for jan then for feb and march and in a next column just add them up?

Report •

Related Solutions

#4
March 5, 2010 at 12:10:17
quarterly (jan feb and march

=SUM((A2:A7="in")*((B2:B7>=DATEVALUE("jan-1"))*((B2:B7<=DATEVALUE("mar-31"))*C2:C7)))

Remember it's an array formula, use CTRL - SHIFT - ENTER

MIKE

http://www.skeptic.com/


Report •

#5
March 5, 2010 at 12:13:08
Hi,

Another Excel function that works well in this circumstance is SUMPRODUCT()

If your data is in columns A to D, starting at row 2, with dates in column C and you numbers in Column D,
make a results table like this:

	F	G	H		I		J
2			01-Jan-10	01-Apr-10	01-Jul-10
3	Col A	Col B	31-Mar-10	30-Jun-10	30-Sep-10
4	in	S	79		45		0

Cells F4 and G4 can use in-cell drop-downs to enable you to select items in columns A and B respectively (Use data validation to create the drop-downs), and have the first and last dates for each quarter at the top.

Put this formula in cell H4:

=SUMPRODUCT((Sheet1!$A$2:$A$20=$F4)*(Sheet1!$B$2:$B$20=$G4)*(Sheet1!$C$2:$C$20>=H$2)*
(Sheet1!$C$2:$C$20<=H$3)*(Sheet1!$D$2:$D$20))

The formula has been split onto two lines for ease of viewing
Note the $ signs, they are essential for maintaining the correct references when you drag the formula to extend it.
Adjust the end row for your data (in my example the data in columns A to D ended at row 20). Then drag the formula right, under each of the quarters.

You can now change the values in cells F4 and G4 and see the totals for each quarter.

SUMPRODUCT does not have to be entered as an array formula, but it acts as an array formula, working row by row through your data.

Regards
PS a quick way to make an end of quarter date from a start of quarter date is to use the EOMONTH() function.
With 01-Jan-2010 in cell H2, put this in H3:

=EOMONTH(H2,2)
It returns the end of the month, in this case 2 months after the month in H2. If EOMONTH does not work, make sure that the Analysis Toolpack is enabled.
In Excel 2003 click Tools, Add-Ins... and from the dialog box, find Analysis Toolpack and check the box next to it.
In Excel 2007, Add-Ins are accessed through the Office button, then at the bottom, Excel Options, Then the AddIns Tab, then at the bottom, select Excel Add-Ins in the 'Manage' box, click the 'Go...' button, and then find Analysis Toolpack in the dialog box that opens and check the box next to it, (just one of the ways Excel 2007 is so much easier and more intuitive to use than earlier versions!!!!)

Report •

#6
March 8, 2010 at 07:38:26
Thank you for your help. I do have an error though: "The formula in this cell refers to a range that has additional number adjacent to it". The formula is calculating properly, so I don't understand what that error is, or where it is. Results are fine..

Thanks again!
Kat


Report •

#7
March 8, 2010 at 14:15:16
Hi,

I suspect that the 'error' message is actually part of Excel's so called Error Checking which is more a hint system that suggests when you might have unintentionally done something, such as not including a cell next to a group of cells used in the formula, or there is an empty cell in a range of cells containing data.

In Excel 2003, goto Tools - Options and the "Error Checking" tab. Uncheck "Formula omits cells in Region" and see if the message goes away.

In Excel 2007 you do this from the Office Button, - select Excel Options (at the bottom) and then the 'Formulas' Tab, and look in the 'Error Checking Rules' section and uncheck 'Formulas which omit cells in a Region'

Regards


Report •

#8
March 9, 2010 at 09:18:33
that helped :)

Thank you all for your help
Really appreciate

Kat


Report •

Ask Question