how do i use the sum if formula with a date range?
 

Hello, I am trying to sum the total $ amount we are paying on an invoice by
month. I have things on this invoice that go back to January 2004. My dates
are in cells Y2:Y1211 and my totals are in cells AC2:AC1211. I am trying to
do this on a seperate worksheet from all of my data.

Thanks

Hi
do you mean
=SUMIF(Y2:Y1211,"=" & DATE(2004,1,1),AC2:AC1211)-SUMIF(Y2:Y1211,"=" &
DATE(2004,2,1),AC2:AC1211)

For getting the January values

"zach f" wrote:

Hello, I am trying to sum the total $ amount we are paying on an invoice by
month. I have things on this invoice that go back to January 2004. My dates
are in cells Y2:Y1211 and my totals are in cells AC2:AC1211. I am trying to
do this on a seperate worksheet from all of my data.

Thanks

Hi,
You might consider using a pivot table to summarize your data, and then
group it by month. For help in getting started setting up your pivot tables
consider the following sites:
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.cpearson.com/excel/pivots.htm
http://www.contextures.com/xlPivot02.html
http://www.contextures.com/xlPivot01.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm


"zach f" wrote:

Hello, I am trying to sum the total $ amount we are paying on an invoice by
month. I have things on this invoice that go back to January 2004. My dates
are in cells Y2:Y1211 and my totals are in cells AC2:AC1211. I am trying to
do this on a seperate worksheet from all of my data.

Thanks

Well I am not sure I have keyed what I think is the same thing you have below
and I am getting an error message at the AC1211 (The 1st AC1211)...

"Frank Kabel" wrote:

Hi
do you mean
=SUMIF(Y2:Y1211,"=" & DATE(2004,1,1),AC2:AC1211)-SUMIF(Y2:Y1211,"=" &
DATE(2004,2,1),AC2:AC1211)

For getting the January values

"zach f" wrote:

Hello, I am trying to sum the total $ amount we are paying on an invoice by
month. I have things on this invoice that go back to January 2004. My dates
are in cells Y2:Y1211 and my totals are in cells AC2:AC1211. I am trying to
do this on a seperate worksheet from all of my data.

Thanks

Hi Zach

if you're "trying to do this on a seperate worksheet from all of [your]
data." you need to include the sheet names in the ranges e.g.
=SUMIF(Sheet1!Y2:Y1211,"=" &
DATE(2004,1,1),Sheet1!AC2:AC1211)-SUMIF(Sheet1!Y2:Y1211,"=" &
DATE(2004,2,1),Sheet1!AC2:AC1211)

Cheers
JulieD

"zach f" wrote in message
...
Well I am not sure I have keyed what I think is the same thing you have
below
and I am getting an error message at the AC1211 (The 1st AC1211)...

"Frank Kabel" wrote:

Hi
do you mean
=SUMIF(Y2:Y1211,"=" & DATE(2004,1,1),AC2:AC1211)-SUMIF(Y2:Y1211,"=" &
DATE(2004,2,1),AC2:AC1211)

For getting the January values

"zach f" wrote:

Hello, I am trying to sum the total $ amount we are paying on an
invoice by
month. I have things on this invoice that go back to January 2004. My
dates
are in cells Y2:Y1211 and my totals are in cells AC2:AC1211. I am
trying to
do this on a seperate worksheet from all of my data.

Thanks

Correct. I will eventually put it on a seperate spreadsheet but until I get
the formula to work i am putting it on the same worksheet that all my data is
on.

Zach

"JulieD" wrote:

Hi Zach

if you're "trying to do this on a seperate worksheet from all of [your]
data." you need to include the sheet names in the ranges e.g.
=SUMIF(Sheet1!Y2:Y1211,"=" &
DATE(2004,1,1),Sheet1!AC2:AC1211)-SUMIF(Sheet1!Y2:Y1211,"=" &
DATE(2004,2,1),Sheet1!AC2:AC1211)

Cheers
JulieD

"zach f" wrote in message
...
Well I am not sure I have keyed what I think is the same thing you have
below
and I am getting an error message at the AC1211 (The 1st AC1211)...

"Frank Kabel" wrote:

Hi
do you mean
=SUMIF(Y2:Y1211,"=" & DATE(2004,1,1),AC2:AC1211)-SUMIF(Y2:Y1211,"=" &
DATE(2004,2,1),AC2:AC1211)

For getting the January values

"zach f" wrote:

Hello, I am trying to sum the total $ amount we are paying on an
invoice by
month. I have things on this invoice that go back to January 2004. My
dates
are in cells Y2:Y1211 and my totals are in cells AC2:AC1211. I am
trying to
do this on a seperate worksheet from all of my data.

Thanks

Hi Zach

could you copy & paste your formula into a post as there appears (to me) to
be nothing wrong with the formula Frank has give you. Additionally, what
version of excel are you using - if it's 2002 or 2003 there's a handy tool
under tools / formula auditing / evaluate formula
which you can use to step through a formula to see where it goes wrong ..
maybe that will give you a clue.

Cheers
JulieD

"zach f" wrote in message
...
Correct. I will eventually put it on a seperate spreadsheet but until I
get
the formula to work i am putting it on the same worksheet that all my data
is
on.

Zach

"JulieD" wrote:

Hi Zach

if you're "trying to do this on a seperate worksheet from all of [your]
data." you need to include the sheet names in the ranges e.g.
=SUMIF(Sheet1!Y2:Y1211,"=" &
DATE(2004,1,1),Sheet1!AC2:AC1211)-SUMIF(Sheet1!Y2:Y1211,"=" &
DATE(2004,2,1),Sheet1!AC2:AC1211)

Cheers
JulieD

"zach f" wrote in message
...
Well I am not sure I have keyed what I think is the same thing you have
below
and I am getting an error message at the AC1211 (The 1st AC1211)...

"Frank Kabel" wrote:

Hi
do you mean
=SUMIF(Y2:Y1211,"=" & DATE(2004,1,1),AC2:AC1211)-SUMIF(Y2:Y1211,"="
&
DATE(2004,2,1),AC2:AC1211)

For getting the January values

"zach f" wrote:

Hello, I am trying to sum the total $ amount we are paying on an
invoice by
month. I have things on this invoice that go back to January 2004.
My
dates
are in cells Y2:Y1211 and my totals are in cells AC2:AC1211. I am
trying to
do this on a seperate worksheet from all of my data.

Thanks

I tried to paste it in the cell that did not work. I just pasted it in the
box up above (whatever its called) and it worked.

Thanks
Zach

"JulieD" wrote:

Hi Zach

could you copy & paste your formula into a post as there appears (to me) to
be nothing wrong with the formula Frank has give you. Additionally, what
version of excel are you using - if it's 2002 or 2003 there's a handy tool
under tools / formula auditing / evaluate formula
which you can use to step through a formula to see where it goes wrong ..
maybe that will give you a clue.

Cheers
JulieD

"zach f" wrote in message
...
Correct. I will eventually put it on a seperate spreadsheet but until I
get
the formula to work i am putting it on the same worksheet that all my data
is
on.

Zach

"JulieD" wrote:

Hi Zach

if you're "trying to do this on a seperate worksheet from all of [your]
data." you need to include the sheet names in the ranges e.g.
=SUMIF(Sheet1!Y2:Y1211,"=" &
DATE(2004,1,1),Sheet1!AC2:AC1211)-SUMIF(Sheet1!Y2:Y1211,"=" &
DATE(2004,2,1),Sheet1!AC2:AC1211)

Cheers
JulieD

"zach f" wrote in message
...
Well I am not sure I have keyed what I think is the same thing you have
below
and I am getting an error message at the AC1211 (The 1st AC1211)...

"Frank Kabel" wrote:

Hi
do you mean
=SUMIF(Y2:Y1211,"=" & DATE(2004,1,1),AC2:AC1211)-SUMIF(Y2:Y1211,"="
&
DATE(2004,2,1),AC2:AC1211)

For getting the January values

"zach f" wrote:

Hello, I am trying to sum the total $ amount we are paying on an
invoice by
month. I have things on this invoice that go back to January 2004.
My
dates
are in cells Y2:Y1211 and my totals are in cells AC2:AC1211. I am
trying to
do this on a seperate worksheet from all of my data.

Thanks

formula bar?

glad it works.

Cheers
JulieD

"zach f" wrote in message
...
I tried to paste it in the cell that did not work. I just pasted it in the
box up above (whatever its called) and it worked.

Thanks
Zach

"JulieD" wrote:

Hi Zach

could you copy & paste your formula into a post as there appears (to me)
to
be nothing wrong with the formula Frank has give you. Additionally, what
version of excel are you using - if it's 2002 or 2003 there's a handy
tool
under tools / formula auditing / evaluate formula
which you can use to step through a formula to see where it goes wrong ..
maybe that will give you a clue.

Cheers
JulieD

"zach f" wrote in message
...
Correct. I will eventually put it on a seperate spreadsheet but until
I
get
the formula to work i am putting it on the same worksheet that all my
data
is
on.

Zach

"JulieD" wrote:

Hi Zach

if you're "trying to do this on a seperate worksheet from all of
[your]
data." you need to include the sheet names in the ranges e.g.
=SUMIF(Sheet1!Y2:Y1211,"=" &
DATE(2004,1,1),Sheet1!AC2:AC1211)-SUMIF(Sheet1!Y2:Y1211,"=" &
DATE(2004,2,1),Sheet1!AC2:AC1211)

Cheers
JulieD

"zach f" wrote in message
...
Well I am not sure I have keyed what I think is the same thing you
have
below
and I am getting an error message at the AC1211 (The 1st AC1211)...

"Frank Kabel" wrote:

Hi
do you mean
=SUMIF(Y2:Y1211,"=" &
DATE(2004,1,1),AC2:AC1211)-SUMIF(Y2:Y1211,"="
&
DATE(2004,2,1),AC2:AC1211)

For getting the January values

"zach f" wrote:

Hello, I am trying to sum the total $ amount we are paying on an
invoice by
month. I have things on this invoice that go back to January
2004.
My
dates
are in cells Y2:Y1211 and my totals are in cells AC2:AC1211. I
am
trying to
do this on a seperate worksheet from all of my data.

Thanks

Yes thats it. One more question. My October total is not correct.
This is my formula...what would cause this to be off...350.78?

=SUMIF('133281lodging1'!Y11:Y1220,"=" &
DATE(2004,10,1),'133281lodging1'!AC11:AC1220)-SUMIF('133281lodging1'!Y11:Y1220,"=" &
DATE(2004,11,1),'133281lodging1'!AC11:AC1220)

"JulieD" wrote:

formula bar?

glad it works.

Cheers
JulieD

"zach f" wrote in message
...
I tried to paste it in the cell that did not work. I just pasted it in the
box up above (whatever its called) and it worked.

Thanks
Zach

"JulieD" wrote:

Hi Zach

could you copy & paste your formula into a post as there appears (to me)
to
be nothing wrong with the formula Frank has give you. Additionally, what
version of excel are you using - if it's 2002 or 2003 there's a handy
tool
under tools / formula auditing / evaluate formula
which you can use to step through a formula to see where it goes wrong ..
maybe that will give you a clue.

Cheers
JulieD

"zach f" wrote in message
...
Correct. I will eventually put it on a seperate spreadsheet but until
I
get
the formula to work i am putting it on the same worksheet that all my
data
is
on.

Zach

"JulieD" wrote:

Hi Zach

if you're "trying to do this on a seperate worksheet from all of
[your]
data." you need to include the sheet names in the ranges e.g.
=SUMIF(Sheet1!Y2:Y1211,"=" &
DATE(2004,1,1),Sheet1!AC2:AC1211)-SUMIF(Sheet1!Y2:Y1211,"=" &
DATE(2004,2,1),Sheet1!AC2:AC1211)

Cheers
JulieD

"zach f" wrote in message
...
Well I am not sure I have keyed what I think is the same thing you
have
below
and I am getting an error message at the AC1211 (The 1st AC1211)...

"Frank Kabel" wrote:

Hi
do you mean
=SUMIF(Y2:Y1211,"=" &
DATE(2004,1,1),AC2:AC1211)-SUMIF(Y2:Y1211,"="
&
DATE(2004,2,1),AC2:AC1211)

For getting the January values

"zach f" wrote:

Hello, I am trying to sum the total $ amount we are paying on an
invoice by
month. I have things on this invoice that go back to January
2004.
My
dates
are in cells Y2:Y1211 and my totals are in cells AC2:AC1211. I
am
trying to
do this on a seperate worksheet from all of my data.

Thanks

the formula works perfectly for me ... so check that all your dates are
entered as dates
select the column choose format / cells / number tab - tick general ... all
the numbers should go to something like 38261 to 38303 or thereabouts if you
have decimals please let me know
(you can then do format / cells / number tab - choose date format again)
then check that all your numbers are numbers - in a column to the right of
the numbers type
=ISNUMBER(AC11) and fill down - if there are any FALSE then it is text.
(if all true you can delete this column). If there are false then select a
blank, unused cell and copy it, click on the "number" that is returning
FALSE and choose edit / paste special - ADD
and then it should behave.

Hope this helps
Cheers
JulieD


"zach f" wrote in message
...
Yes thats it. One more question. My October total is not correct.
This is my formula...what would cause this to be off...350.78?

=SUMIF('133281lodging1'!Y11:Y1220,"=" &
DATE(2004,10,1),'133281lodging1'!AC11:AC1220)-SUMIF('133281lodging1'!Y11:Y1220,"="
&
DATE(2004,11,1),'133281lodging1'!AC11:AC1220)

"JulieD" wrote:

formula bar?

glad it works.

Cheers
JulieD

"zach f" wrote in message
...
I tried to paste it in the cell that did not work. I just pasted it in
the
box up above (whatever its called) and it worked.

Thanks
Zach

"JulieD" wrote:

Hi Zach

could you copy & paste your formula into a post as there appears (to
me)
to
be nothing wrong with the formula Frank has give you. Additionally,
what
version of excel are you using - if it's 2002 or 2003 there's a handy
tool
under tools / formula auditing / evaluate formula
which you can use to step through a formula to see where it goes wrong
..
maybe that will give you a clue.

Cheers
JulieD

"zach f" wrote in message
...
Correct. I will eventually put it on a seperate spreadsheet but
until
I
get
the formula to work i am putting it on the same worksheet that all
my
data
is
on.

Zach

"JulieD" wrote:

Hi Zach

if you're "trying to do this on a seperate worksheet from all of
[your]
data." you need to include the sheet names in the ranges e.g.
=SUMIF(Sheet1!Y2:Y1211,"=" &
DATE(2004,1,1),Sheet1!AC2:AC1211)-SUMIF(Sheet1!Y2:Y1211,"=" &
DATE(2004,2,1),Sheet1!AC2:AC1211)

Cheers
JulieD

"zach f" wrote in message
...
Well I am not sure I have keyed what I think is the same thing
you
have
below
and I am getting an error message at the AC1211 (The 1st
AC1211)...

"Frank Kabel" wrote:

Hi
do you mean
=SUMIF(Y2:Y1211,"=" &
DATE(2004,1,1),AC2:AC1211)-SUMIF(Y2:Y1211,"="
&
DATE(2004,2,1),AC2:AC1211)

For getting the January values

"zach f" wrote:

Hello, I am trying to sum the total $ amount we are paying on
an
invoice by
month. I have things on this invoice that go back to January
2004.
My
dates
are in cells Y2:Y1211 and my totals are in cells AC2:AC1211.
I
am
trying to
do this on a seperate worksheet from all of my data.

Thanks