SumIf help needed plz...

Here is my issue.

I am using this formula to search a linked sheet to confirm that an account is older than 120 days and sum the balance on those accounts. Works great, except one problem as of tomorrow the number for the current date is no longer accurate therefore the 120 days isn't accurate.

=SUMIF('[Account 2005.xls]June'!$A:$A,"<38410",'[Account 2005.xls]June'!$C:$C)

So then I came up with this solution:

=SUMIF('[Account 2005.xls]June'!$A:$A,"<(TODAY-120)",'[Account 2005.xls]June'!$C:$C)

This gives me $0.00 everytime. I tested the today-120 formula by itself and it works fine. Not sure if the problem is the order of the operation or some sort of loop problem. If this is not the correct resolution does anyone have any ideas.

I also did the date calculation in a seperate cell and did a formula to point at that cell i.e. <J2 but it didn't work either.

Thanks for your help.

Mike W.
Nashville

=SUMIF('[Account
2005.xls]June'!$A:$A,"<"&(TODAY()-120),'[Account2005.xls]June'!$C:$C)

--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> Here is my issue.
>
> I am using this formula to search a linked sheet to confirm that an
> account is older than 120 days and sum the balance on those accounts.
> Works great, except one problem as of tomorrow the number for the
> current date is no longer accurate therefore the 120 days isn't
> accurate.
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<38410",'[Account
> 2005.xls]June'!$C:$C)
>
> So then I came up with this solution:
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<(TODAY-120)",'[Account
> 2005.xls]June'!$C:$C)
>
> This gives me $0.00 everytime. I tested the today-120 formula by
> itself and it works fine. Not sure if the problem is the order of the
> operation or some sort of loop problem. If this is not the correct
> resolution does anyone have any ideas.
>
> I also did the date calculation in a seperate cell and did a formula to
> point at that cell i.e. <J2 but it didn't work either.
>
> Thanks for your help.
>
> Mike W.
> Nashville
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>

try
=SUMIF('[Account 2005.xls]June'!$A:$A,"<" & datevalue(today()) &
"-90",'[Account
2005.xls]June'!$C:$C)

"miwarren" wrote:

>
> Here is my issue.
>
> I am using this formula to search a linked sheet to confirm that an
> account is older than 120 days and sum the balance on those accounts.
> Works great, except one problem as of tomorrow the number for the
> current date is no longer accurate therefore the 120 days isn't
> accurate.
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<38410",'[Account
> 2005.xls]June'!$C:$C)
>
> So then I came up with this solution:
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<(TODAY-120)",'[Account
> 2005.xls]June'!$C:$C)
>
> This gives me $0.00 everytime. I tested the today-120 formula by
> itself and it works fine. Not sure if the problem is the order of the
> operation or some sort of loop problem. If this is not the correct
> resolution does anyone have any ideas.
>
> I also did the date calculation in a seperate cell and did a formula to
> point at that cell i.e. <J2 but it didn't work either.
>
> Thanks for your help.
>
> Mike W.
> Nashville
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile: http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>
>

I did as you said Bob and it worked, if the workbook that contained the source was open. I had this problem earlier because it wouldn't pull the linked data unless you opened the source document. So I created a macro that would open and close all documents that were linked to the summary document therefore loading the linked content. That was the fix I was using and it worked with the first formula that I posted. Well, in the cells that I used the formula you suggested it would load the data as long as the source was open but as soon as the source was closed it went back to #VALUE!. ugh... Any suggestions??? I really do appreciate your help and patience. It is starting to overwhelm me.

Your solution is a bit drastic <G>

I should have realised that problem and given you a formula that would last
the closed workbooks. Try this instead

=SUMPRODUCT(--('[Account
2005.xls]June'!$A1:$A1000<"TODAY()-120),'[Account2005.xls]June'!$C1:$C1000)

--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> I did as you said Bob and it worked, *if* the workbook that contained
> the source was open. I had this problem earlier because it wouldn't
> pull the linked data unless you opened the source document. So I
> created a macro that would open and close all documents that were
> linked to the summary document therefore loading the linked content.
> That was the fix I was using and it worked with the first formula that
> I posted. Well, in the cells that I used the formula you suggested it
> would load the data as long as the source was open but as soon as the
> source was closed it went back to #VALUE!. ugh... Any suggestions???
> I really do appreciate your help and patience. It is starting to
> overwhelm me.
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>

I entered it like this and now I get a #NUM! error message. Any other help you can provide?

=SUMPRODUCT('[Account 2005.xls]June'!$A:$A,"<"&TODAY()-120,'[Account 2005.xls]June'!$C:$C)

Thanks again!!!

Try...

=SUMPRODUCT(--('[Account 2005.xls]June'!$A$2:$A$65536<TOTAY()-120),'[Account 2005.xls]June'!$C$2:$C$65536)

Note that SUMPRODUCT does not allow whole column references. But you can use 'near' whole column references, as per above formula.

Hope this helps!

Originally Posted by miwarren

I entered it like this and now I get a #NUM! error message. Any other help you can provide?

=SUMPRODUCT('[Account 2005.xls]June'!$A:$A,"<"&TODAY()-120,'[Account 2005.xls]June'!$C:$C)

Thanks again!!!

Sorry, clumsiness. SUMPRODUCT only works on part of a column, not the whole
column, so try

=SUMPRODUCT('[Account
2005.xls]June'!$A1:$A1000,"<"&TODAY()-120,'[Account2005.xls]June'!$C1:$C1000
)

or howver many rows that you have.


--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> I entered it like this and now I get a #NUM! error message. Any other
> help you can provide?
>
> =SUMPRODUCT('[Account 2005.xls]June'!$A:$A,"<"&TODAY()-120,'[Account
> 2005.xls]June'!$C:$C)
>
> Thanks again!!!
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>

=SUMIF('[Account
2005.xls]June'!$A:$A,"<"&(TODAY()-120),'[Account2005.xls]June'!$C:$C)

--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> Here is my issue.
>
> I am using this formula to search a linked sheet to confirm that an
> account is older than 120 days and sum the balance on those accounts.
> Works great, except one problem as of tomorrow the number for the
> current date is no longer accurate therefore the 120 days isn't
> accurate.
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<38410",'[Account
> 2005.xls]June'!$C:$C)
>
> So then I came up with this solution:
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<(TODAY-120)",'[Account
> 2005.xls]June'!$C:$C)
>
> This gives me $0.00 everytime. I tested the today-120 formula by
> itself and it works fine. Not sure if the problem is the order of the
> operation or some sort of loop problem. If this is not the correct
> resolution does anyone have any ideas.
>
> I also did the date calculation in a seperate cell and did a formula to
> point at that cell i.e. <J2 but it didn't work either.
>
> Thanks for your help.
>
> Mike W.
> Nashville
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>

try
=SUMIF('[Account 2005.xls]June'!$A:$A,"<" & datevalue(today()) &
"-90",'[Account
2005.xls]June'!$C:$C)

"miwarren" wrote:

>
> Here is my issue.
>
> I am using this formula to search a linked sheet to confirm that an
> account is older than 120 days and sum the balance on those accounts.
> Works great, except one problem as of tomorrow the number for the
> current date is no longer accurate therefore the 120 days isn't
> accurate.
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<38410",'[Account
> 2005.xls]June'!$C:$C)
>
> So then I came up with this solution:
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<(TODAY-120)",'[Account
> 2005.xls]June'!$C:$C)
>
> This gives me $0.00 everytime. I tested the today-120 formula by
> itself and it works fine. Not sure if the problem is the order of the
> operation or some sort of loop problem. If this is not the correct
> resolution does anyone have any ideas.
>
> I also did the date calculation in a seperate cell and did a formula to
> point at that cell i.e. <J2 but it didn't work either.
>
> Thanks for your help.
>
> Mike W.
> Nashville
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile: http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>
>

Your solution is a bit drastic <G>

I should have realised that problem and given you a formula that would last
the closed workbooks. Try this instead

=SUMPRODUCT(--('[Account
2005.xls]June'!$A1:$A1000<"TODAY()-120),'[Account2005.xls]June'!$C1:$C1000)

--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> I did as you said Bob and it worked, *if* the workbook that contained
> the source was open. I had this problem earlier because it wouldn't
> pull the linked data unless you opened the source document. So I
> created a macro that would open and close all documents that were
> linked to the summary document therefore loading the linked content.
> That was the fix I was using and it worked with the first formula that
> I posted. Well, in the cells that I used the formula you suggested it
> would load the data as long as the source was open but as soon as the
> source was closed it went back to #VALUE!. ugh... Any suggestions???
> I really do appreciate your help and patience. It is starting to
> overwhelm me.
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>

Sorry, clumsiness. SUMPRODUCT only works on part of a column, not the whole
column, so try

=SUMPRODUCT('[Account
2005.xls]June'!$A1:$A1000,"<"&TODAY()-120,'[Account2005.xls]June'!$C1:$C1000
)

or howver many rows that you have.


--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> I entered it like this and now I get a #NUM! error message. Any other
> help you can provide?
>
> =SUMPRODUCT('[Account 2005.xls]June'!$A:$A,"<"&TODAY()-120,'[Account
> 2005.xls]June'!$C:$C)
>
> Thanks again!!!
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>

Your solution is a bit drastic <G>

I should have realised that problem and given you a formula that would last
the closed workbooks. Try this instead

=SUMPRODUCT(--('[Account
2005.xls]June'!$A1:$A1000<"TODAY()-120),'[Account2005.xls]June'!$C1:$C1000)

--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> I did as you said Bob and it worked, *if* the workbook that contained
> the source was open. I had this problem earlier because it wouldn't
> pull the linked data unless you opened the source document. So I
> created a macro that would open and close all documents that were
> linked to the summary document therefore loading the linked content.
> That was the fix I was using and it worked with the first formula that
> I posted. Well, in the cells that I used the formula you suggested it
> would load the data as long as the source was open but as soon as the
> source was closed it went back to #VALUE!. ugh... Any suggestions???
> I really do appreciate your help and patience. It is starting to
> overwhelm me.
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>

Sorry, clumsiness. SUMPRODUCT only works on part of a column, not the whole
column, so try

=SUMPRODUCT('[Account
2005.xls]June'!$A1:$A1000,"<"&TODAY()-120,'[Account2005.xls]June'!$C1:$C1000
)

or howver many rows that you have.


--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> I entered it like this and now I get a #NUM! error message. Any other
> help you can provide?
>
> =SUMPRODUCT('[Account 2005.xls]June'!$A:$A,"<"&TODAY()-120,'[Account
> 2005.xls]June'!$C:$C)
>
> Thanks again!!!
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>

try
=SUMIF('[Account 2005.xls]June'!$A:$A,"<" & datevalue(today()) &
"-90",'[Account
2005.xls]June'!$C:$C)

"miwarren" wrote:

>
> Here is my issue.
>
> I am using this formula to search a linked sheet to confirm that an
> account is older than 120 days and sum the balance on those accounts.
> Works great, except one problem as of tomorrow the number for the
> current date is no longer accurate therefore the 120 days isn't
> accurate.
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<38410",'[Account
> 2005.xls]June'!$C:$C)
>
> So then I came up with this solution:
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<(TODAY-120)",'[Account
> 2005.xls]June'!$C:$C)
>
> This gives me $0.00 everytime. I tested the today-120 formula by
> itself and it works fine. Not sure if the problem is the order of the
> operation or some sort of loop problem. If this is not the correct
> resolution does anyone have any ideas.
>
> I also did the date calculation in a seperate cell and did a formula to
> point at that cell i.e. <J2 but it didn't work either.
>
> Thanks for your help.
>
> Mike W.
> Nashville
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile: http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>
>

=SUMIF('[Account
2005.xls]June'!$A:$A,"<"&(TODAY()-120),'[Account2005.xls]June'!$C:$C)

--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> Here is my issue.
>
> I am using this formula to search a linked sheet to confirm that an
> account is older than 120 days and sum the balance on those accounts.
> Works great, except one problem as of tomorrow the number for the
> current date is no longer accurate therefore the 120 days isn't
> accurate.
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<38410",'[Account
> 2005.xls]June'!$C:$C)
>
> So then I came up with this solution:
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<(TODAY-120)",'[Account
> 2005.xls]June'!$C:$C)
>
> This gives me $0.00 everytime. I tested the today-120 formula by
> itself and it works fine. Not sure if the problem is the order of the
> operation or some sort of loop problem. If this is not the correct
> resolution does anyone have any ideas.
>
> I also did the date calculation in a seperate cell and did a formula to
> point at that cell i.e. <J2 but it didn't work either.
>
> Thanks for your help.
>
> Mike W.
> Nashville
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>

Sorry, clumsiness. SUMPRODUCT only works on part of a column, not the whole
column, so try

=SUMPRODUCT('[Account
2005.xls]June'!$A1:$A1000,"<"&TODAY()-120,'[Account2005.xls]June'!$C1:$C1000
)

or howver many rows that you have.


--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> I entered it like this and now I get a #NUM! error message. Any other
> help you can provide?
>
> =SUMPRODUCT('[Account 2005.xls]June'!$A:$A,"<"&TODAY()-120,'[Account
> 2005.xls]June'!$C:$C)
>
> Thanks again!!!
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>

Your solution is a bit drastic <G>

I should have realised that problem and given you a formula that would last
the closed workbooks. Try this instead

=SUMPRODUCT(--('[Account
2005.xls]June'!$A1:$A1000<"TODAY()-120),'[Account2005.xls]June'!$C1:$C1000)

--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> I did as you said Bob and it worked, *if* the workbook that contained
> the source was open. I had this problem earlier because it wouldn't
> pull the linked data unless you opened the source document. So I
> created a macro that would open and close all documents that were
> linked to the summary document therefore loading the linked content.
> That was the fix I was using and it worked with the first formula that
> I posted. Well, in the cells that I used the formula you suggested it
> would load the data as long as the source was open but as soon as the
> source was closed it went back to #VALUE!. ugh... Any suggestions???
> I really do appreciate your help and patience. It is starting to
> overwhelm me.
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>

=SUMIF('[Account
2005.xls]June'!$A:$A,"<"&(TODAY()-120),'[Account2005.xls]June'!$C:$C)

--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> Here is my issue.
>
> I am using this formula to search a linked sheet to confirm that an
> account is older than 120 days and sum the balance on those accounts.
> Works great, except one problem as of tomorrow the number for the
> current date is no longer accurate therefore the 120 days isn't
> accurate.
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<38410",'[Account
> 2005.xls]June'!$C:$C)
>
> So then I came up with this solution:
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<(TODAY-120)",'[Account
> 2005.xls]June'!$C:$C)
>
> This gives me $0.00 everytime. I tested the today-120 formula by
> itself and it works fine. Not sure if the problem is the order of the
> operation or some sort of loop problem. If this is not the correct
> resolution does anyone have any ideas.
>
> I also did the date calculation in a seperate cell and did a formula to
> point at that cell i.e. <J2 but it didn't work either.
>
> Thanks for your help.
>
> Mike W.
> Nashville
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>

try
=SUMIF('[Account 2005.xls]June'!$A:$A,"<" & datevalue(today()) &
"-90",'[Account
2005.xls]June'!$C:$C)

"miwarren" wrote:

>
> Here is my issue.
>
> I am using this formula to search a linked sheet to confirm that an
> account is older than 120 days and sum the balance on those accounts.
> Works great, except one problem as of tomorrow the number for the
> current date is no longer accurate therefore the 120 days isn't
> accurate.
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<38410",'[Account
> 2005.xls]June'!$C:$C)
>
> So then I came up with this solution:
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<(TODAY-120)",'[Account
> 2005.xls]June'!$C:$C)
>
> This gives me $0.00 everytime. I tested the today-120 formula by
> itself and it works fine. Not sure if the problem is the order of the
> operation or some sort of loop problem. If this is not the correct
> resolution does anyone have any ideas.
>
> I also did the date calculation in a seperate cell and did a formula to
> point at that cell i.e. <J2 but it didn't work either.
>
> Thanks for your help.
>
> Mike W.
> Nashville
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile: http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>
>

Your solution is a bit drastic <G>

I should have realised that problem and given you a formula that would last
the closed workbooks. Try this instead

=SUMPRODUCT(--('[Account
2005.xls]June'!$A1:$A1000<"TODAY()-120),'[Account2005.xls]June'!$C1:$C1000)

--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> I did as you said Bob and it worked, *if* the workbook that contained
> the source was open. I had this problem earlier because it wouldn't
> pull the linked data unless you opened the source document. So I
> created a macro that would open and close all documents that were
> linked to the summary document therefore loading the linked content.
> That was the fix I was using and it worked with the first formula that
> I posted. Well, in the cells that I used the formula you suggested it
> would load the data as long as the source was open but as soon as the
> source was closed it went back to #VALUE!. ugh... Any suggestions???
> I really do appreciate your help and patience. It is starting to
> overwhelm me.
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>

try
=SUMIF('[Account 2005.xls]June'!$A:$A,"<" & datevalue(today()) &
"-90",'[Account
2005.xls]June'!$C:$C)

"miwarren" wrote:

>
> Here is my issue.
>
> I am using this formula to search a linked sheet to confirm that an
> account is older than 120 days and sum the balance on those accounts.
> Works great, except one problem as of tomorrow the number for the
> current date is no longer accurate therefore the 120 days isn't
> accurate.
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<38410",'[Account
> 2005.xls]June'!$C:$C)
>
> So then I came up with this solution:
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<(TODAY-120)",'[Account
> 2005.xls]June'!$C:$C)
>
> This gives me $0.00 everytime. I tested the today-120 formula by
> itself and it works fine. Not sure if the problem is the order of the
> operation or some sort of loop problem. If this is not the correct
> resolution does anyone have any ideas.
>
> I also did the date calculation in a seperate cell and did a formula to
> point at that cell i.e. <J2 but it didn't work either.
>
> Thanks for your help.
>
> Mike W.
> Nashville
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile: http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>
>

=SUMIF('[Account
2005.xls]June'!$A:$A,"<"&(TODAY()-120),'[Account2005.xls]June'!$C:$C)

--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> Here is my issue.
>
> I am using this formula to search a linked sheet to confirm that an
> account is older than 120 days and sum the balance on those accounts.
> Works great, except one problem as of tomorrow the number for the
> current date is no longer accurate therefore the 120 days isn't
> accurate.
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<38410",'[Account
> 2005.xls]June'!$C:$C)
>
> So then I came up with this solution:
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<(TODAY-120)",'[Account
> 2005.xls]June'!$C:$C)
>
> This gives me $0.00 everytime. I tested the today-120 formula by
> itself and it works fine. Not sure if the problem is the order of the
> operation or some sort of loop problem. If this is not the correct
> resolution does anyone have any ideas.
>
> I also did the date calculation in a seperate cell and did a formula to
> point at that cell i.e. <J2 but it didn't work either.
>
> Thanks for your help.
>
> Mike W.
> Nashville
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>

Sorry, clumsiness. SUMPRODUCT only works on part of a column, not the whole
column, so try

=SUMPRODUCT('[Account
2005.xls]June'!$A1:$A1000,"<"&TODAY()-120,'[Account2005.xls]June'!$C1:$C1000
)

or howver many rows that you have.


--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> I entered it like this and now I get a #NUM! error message. Any other
> help you can provide?
>
> =SUMPRODUCT('[Account 2005.xls]June'!$A:$A,"<"&TODAY()-120,'[Account
> 2005.xls]June'!$C:$C)
>
> Thanks again!!!
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>

Your solution is a bit drastic <G>

I should have realised that problem and given you a formula that would last
the closed workbooks. Try this instead

=SUMPRODUCT(--('[Account
2005.xls]June'!$A1:$A1000<"TODAY()-120),'[Account2005.xls]June'!$C1:$C1000)

--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> I did as you said Bob and it worked, *if* the workbook that contained
> the source was open. I had this problem earlier because it wouldn't
> pull the linked data unless you opened the source document. So I
> created a macro that would open and close all documents that were
> linked to the summary document therefore loading the linked content.
> That was the fix I was using and it worked with the first formula that
> I posted. Well, in the cells that I used the formula you suggested it
> would load the data as long as the source was open but as soon as the
> source was closed it went back to #VALUE!. ugh... Any suggestions???
> I really do appreciate your help and patience. It is starting to
> overwhelm me.
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>

try
=SUMIF('[Account 2005.xls]June'!$A:$A,"<" & datevalue(today()) &
"-90",'[Account
2005.xls]June'!$C:$C)

"miwarren" wrote:

>
> Here is my issue.
>
> I am using this formula to search a linked sheet to confirm that an
> account is older than 120 days and sum the balance on those accounts.
> Works great, except one problem as of tomorrow the number for the
> current date is no longer accurate therefore the 120 days isn't
> accurate.
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<38410",'[Account
> 2005.xls]June'!$C:$C)
>
> So then I came up with this solution:
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<(TODAY-120)",'[Account
> 2005.xls]June'!$C:$C)
>
> This gives me $0.00 everytime. I tested the today-120 formula by
> itself and it works fine. Not sure if the problem is the order of the
> operation or some sort of loop problem. If this is not the correct
> resolution does anyone have any ideas.
>
> I also did the date calculation in a seperate cell and did a formula to
> point at that cell i.e. <J2 but it didn't work either.
>
> Thanks for your help.
>
> Mike W.
> Nashville
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile: http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>
>

Sorry, clumsiness. SUMPRODUCT only works on part of a column, not the whole
column, so try

=SUMPRODUCT('[Account
2005.xls]June'!$A1:$A1000,"<"&TODAY()-120,'[Account2005.xls]June'!$C1:$C1000
)

or howver many rows that you have.


--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> I entered it like this and now I get a #NUM! error message. Any other
> help you can provide?
>
> =SUMPRODUCT('[Account 2005.xls]June'!$A:$A,"<"&TODAY()-120,'[Account
> 2005.xls]June'!$C:$C)
>
> Thanks again!!!
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>

=SUMIF('[Account
2005.xls]June'!$A:$A,"<"&(TODAY()-120),'[Account2005.xls]June'!$C:$C)

--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> Here is my issue.
>
> I am using this formula to search a linked sheet to confirm that an
> account is older than 120 days and sum the balance on those accounts.
> Works great, except one problem as of tomorrow the number for the
> current date is no longer accurate therefore the 120 days isn't
> accurate.
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<38410",'[Account
> 2005.xls]June'!$C:$C)
>
> So then I came up with this solution:
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<(TODAY-120)",'[Account
> 2005.xls]June'!$C:$C)
>
> This gives me $0.00 everytime. I tested the today-120 formula by
> itself and it works fine. Not sure if the problem is the order of the
> operation or some sort of loop problem. If this is not the correct
> resolution does anyone have any ideas.
>
> I also did the date calculation in a seperate cell and did a formula to
> point at that cell i.e. <J2 but it didn't work either.
>
> Thanks for your help.
>
> Mike W.
> Nashville
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>

Your solution is a bit drastic <G>

I should have realised that problem and given you a formula that would last
the closed workbooks. Try this instead

=SUMPRODUCT(--('[Account
2005.xls]June'!$A1:$A1000<"TODAY()-120),'[Account2005.xls]June'!$C1:$C1000)

--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> I did as you said Bob and it worked, *if* the workbook that contained
> the source was open. I had this problem earlier because it wouldn't
> pull the linked data unless you opened the source document. So I
> created a macro that would open and close all documents that were
> linked to the summary document therefore loading the linked content.
> That was the fix I was using and it worked with the first formula that
> I posted. Well, in the cells that I used the formula you suggested it
> would load the data as long as the source was open but as soon as the
> source was closed it went back to #VALUE!. ugh... Any suggestions???
> I really do appreciate your help and patience. It is starting to
> overwhelm me.
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>

Sorry, clumsiness. SUMPRODUCT only works on part of a column, not the whole
column, so try

=SUMPRODUCT('[Account
2005.xls]June'!$A1:$A1000,"<"&TODAY()-120,'[Account2005.xls]June'!$C1:$C1000
)

or howver many rows that you have.


--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> I entered it like this and now I get a #NUM! error message. Any other
> help you can provide?
>
> =SUMPRODUCT('[Account 2005.xls]June'!$A:$A,"<"&TODAY()-120,'[Account
> 2005.xls]June'!$C:$C)
>
> Thanks again!!!
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>

try
=SUMIF('[Account 2005.xls]June'!$A:$A,"<" & datevalue(today()) &
"-90",'[Account
2005.xls]June'!$C:$C)

"miwarren" wrote:

>
> Here is my issue.
>
> I am using this formula to search a linked sheet to confirm that an
> account is older than 120 days and sum the balance on those accounts.
> Works great, except one problem as of tomorrow the number for the
> current date is no longer accurate therefore the 120 days isn't
> accurate.
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<38410",'[Account
> 2005.xls]June'!$C:$C)
>
> So then I came up with this solution:
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<(TODAY-120)",'[Account
> 2005.xls]June'!$C:$C)
>
> This gives me $0.00 everytime. I tested the today-120 formula by
> itself and it works fine. Not sure if the problem is the order of the
> operation or some sort of loop problem. If this is not the correct
> resolution does anyone have any ideas.
>
> I also did the date calculation in a seperate cell and did a formula to
> point at that cell i.e. <J2 but it didn't work either.
>
> Thanks for your help.
>
> Mike W.
> Nashville
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile: http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>
>

=SUMIF('[Account
2005.xls]June'!$A:$A,"<"&(TODAY()-120),'[Account2005.xls]June'!$C:$C)

--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> Here is my issue.
>
> I am using this formula to search a linked sheet to confirm that an
> account is older than 120 days and sum the balance on those accounts.
> Works great, except one problem as of tomorrow the number for the
> current date is no longer accurate therefore the 120 days isn't
> accurate.
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<38410",'[Account
> 2005.xls]June'!$C:$C)
>
> So then I came up with this solution:
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<(TODAY-120)",'[Account
> 2005.xls]June'!$C:$C)
>
> This gives me $0.00 everytime. I tested the today-120 formula by
> itself and it works fine. Not sure if the problem is the order of the
> operation or some sort of loop problem. If this is not the correct
> resolution does anyone have any ideas.
>
> I also did the date calculation in a seperate cell and did a formula to
> point at that cell i.e. <J2 but it didn't work either.
>
> Thanks for your help.
>
> Mike W.
> Nashville
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>

=SUMIF('[Account
2005.xls]June'!$A:$A,"<"&(TODAY()-120),'[Account2005.xls]June'!$C:$C)

--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> Here is my issue.
>
> I am using this formula to search a linked sheet to confirm that an
> account is older than 120 days and sum the balance on those accounts.
> Works great, except one problem as of tomorrow the number for the
> current date is no longer accurate therefore the 120 days isn't
> accurate.
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<38410",'[Account
> 2005.xls]June'!$C:$C)
>
> So then I came up with this solution:
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<(TODAY-120)",'[Account
> 2005.xls]June'!$C:$C)
>
> This gives me $0.00 everytime. I tested the today-120 formula by
> itself and it works fine. Not sure if the problem is the order of the
> operation or some sort of loop problem. If this is not the correct
> resolution does anyone have any ideas.
>
> I also did the date calculation in a seperate cell and did a formula to
> point at that cell i.e. <J2 but it didn't work either.
>
> Thanks for your help.
>
> Mike W.
> Nashville
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>

Sorry, clumsiness. SUMPRODUCT only works on part of a column, not the whole
column, so try

=SUMPRODUCT('[Account
2005.xls]June'!$A1:$A1000,"<"&TODAY()-120,'[Account2005.xls]June'!$C1:$C1000
)

or howver many rows that you have.


--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> I entered it like this and now I get a #NUM! error message. Any other
> help you can provide?
>
> =SUMPRODUCT('[Account 2005.xls]June'!$A:$A,"<"&TODAY()-120,'[Account
> 2005.xls]June'!$C:$C)
>
> Thanks again!!!
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>

try
=SUMIF('[Account 2005.xls]June'!$A:$A,"<" & datevalue(today()) &
"-90",'[Account
2005.xls]June'!$C:$C)

"miwarren" wrote:

>
> Here is my issue.
>
> I am using this formula to search a linked sheet to confirm that an
> account is older than 120 days and sum the balance on those accounts.
> Works great, except one problem as of tomorrow the number for the
> current date is no longer accurate therefore the 120 days isn't
> accurate.
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<38410",'[Account
> 2005.xls]June'!$C:$C)
>
> So then I came up with this solution:
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<(TODAY-120)",'[Account
> 2005.xls]June'!$C:$C)
>
> This gives me $0.00 everytime. I tested the today-120 formula by
> itself and it works fine. Not sure if the problem is the order of the
> operation or some sort of loop problem. If this is not the correct
> resolution does anyone have any ideas.
>
> I also did the date calculation in a seperate cell and did a formula to
> point at that cell i.e. <J2 but it didn't work either.
>
> Thanks for your help.
>
> Mike W.
> Nashville
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile: http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>
>

Your solution is a bit drastic <G>

I should have realised that problem and given you a formula that would last
the closed workbooks. Try this instead

=SUMPRODUCT(--('[Account
2005.xls]June'!$A1:$A1000<"TODAY()-120),'[Account2005.xls]June'!$C1:$C1000)

--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> I did as you said Bob and it worked, *if* the workbook that contained
> the source was open. I had this problem earlier because it wouldn't
> pull the linked data unless you opened the source document. So I
> created a macro that would open and close all documents that were
> linked to the summary document therefore loading the linked content.
> That was the fix I was using and it worked with the first formula that
> I posted. Well, in the cells that I used the formula you suggested it
> would load the data as long as the source was open but as soon as the
> source was closed it went back to #VALUE!. ugh... Any suggestions???
> I really do appreciate your help and patience. It is starting to
> overwhelm me.
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>

Sorry, clumsiness. SUMPRODUCT only works on part of a column, not the whole
column, so try

=SUMPRODUCT('[Account
2005.xls]June'!$A1:$A1000,"<"&TODAY()-120,'[Account2005.xls]June'!$C1:$C1000
)

or howver many rows that you have.


--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> I entered it like this and now I get a #NUM! error message. Any other
> help you can provide?
>
> =SUMPRODUCT('[Account 2005.xls]June'!$A:$A,"<"&TODAY()-120,'[Account
> 2005.xls]June'!$C:$C)
>
> Thanks again!!!
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>

Your solution is a bit drastic <G>

I should have realised that problem and given you a formula that would last
the closed workbooks. Try this instead

=SUMPRODUCT(--('[Account
2005.xls]June'!$A1:$A1000<"TODAY()-120),'[Account2005.xls]June'!$C1:$C1000)

--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> I did as you said Bob and it worked, *if* the workbook that contained
> the source was open. I had this problem earlier because it wouldn't
> pull the linked data unless you opened the source document. So I
> created a macro that would open and close all documents that were
> linked to the summary document therefore loading the linked content.
> That was the fix I was using and it worked with the first formula that
> I posted. Well, in the cells that I used the formula you suggested it
> would load the data as long as the source was open but as soon as the
> source was closed it went back to #VALUE!. ugh... Any suggestions???
> I really do appreciate your help and patience. It is starting to
> overwhelm me.
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>

try
=SUMIF('[Account 2005.xls]June'!$A:$A,"<" & datevalue(today()) &
"-90",'[Account
2005.xls]June'!$C:$C)

"miwarren" wrote:

>
> Here is my issue.
>
> I am using this formula to search a linked sheet to confirm that an
> account is older than 120 days and sum the balance on those accounts.
> Works great, except one problem as of tomorrow the number for the
> current date is no longer accurate therefore the 120 days isn't
> accurate.
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<38410",'[Account
> 2005.xls]June'!$C:$C)
>
> So then I came up with this solution:
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<(TODAY-120)",'[Account
> 2005.xls]June'!$C:$C)
>
> This gives me $0.00 everytime. I tested the today-120 formula by
> itself and it works fine. Not sure if the problem is the order of the
> operation or some sort of loop problem. If this is not the correct
> resolution does anyone have any ideas.
>
> I also did the date calculation in a seperate cell and did a formula to
> point at that cell i.e. <J2 but it didn't work either.
>
> Thanks for your help.
>
> Mike W.
> Nashville
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile: http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>
>

=SUMIF('[Account
2005.xls]June'!$A:$A,"<"&(TODAY()-120),'[Account2005.xls]June'!$C:$C)

--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> Here is my issue.
>
> I am using this formula to search a linked sheet to confirm that an
> account is older than 120 days and sum the balance on those accounts.
> Works great, except one problem as of tomorrow the number for the
> current date is no longer accurate therefore the 120 days isn't
> accurate.
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<38410",'[Account
> 2005.xls]June'!$C:$C)
>
> So then I came up with this solution:
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<(TODAY-120)",'[Account
> 2005.xls]June'!$C:$C)
>
> This gives me $0.00 everytime. I tested the today-120 formula by
> itself and it works fine. Not sure if the problem is the order of the
> operation or some sort of loop problem. If this is not the correct
> resolution does anyone have any ideas.
>
> I also did the date calculation in a seperate cell and did a formula to
> point at that cell i.e. <J2 but it didn't work either.
>
> Thanks for your help.
>
> Mike W.
> Nashville
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>

Sorry, clumsiness. SUMPRODUCT only works on part of a column, not the whole
column, so try

=SUMPRODUCT('[Account
2005.xls]June'!$A1:$A1000,"<"&TODAY()-120,'[Account2005.xls]June'!$C1:$C1000
)

or howver many rows that you have.


--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> I entered it like this and now I get a #NUM! error message. Any other
> help you can provide?
>
> =SUMPRODUCT('[Account 2005.xls]June'!$A:$A,"<"&TODAY()-120,'[Account
> 2005.xls]June'!$C:$C)
>
> Thanks again!!!
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>

Your solution is a bit drastic <G>

I should have realised that problem and given you a formula that would last
the closed workbooks. Try this instead

=SUMPRODUCT(--('[Account
2005.xls]June'!$A1:$A1000<"TODAY()-120),'[Account2005.xls]June'!$C1:$C1000)

--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> I did as you said Bob and it worked, *if* the workbook that contained
> the source was open. I had this problem earlier because it wouldn't
> pull the linked data unless you opened the source document. So I
> created a macro that would open and close all documents that were
> linked to the summary document therefore loading the linked content.
> That was the fix I was using and it worked with the first formula that
> I posted. Well, in the cells that I used the formula you suggested it
> would load the data as long as the source was open but as soon as the
> source was closed it went back to #VALUE!. ugh... Any suggestions???
> I really do appreciate your help and patience. It is starting to
> overwhelm me.
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>

try
=SUMIF('[Account 2005.xls]June'!$A:$A,"<" & datevalue(today()) &
"-90",'[Account
2005.xls]June'!$C:$C)

"miwarren" wrote:

>
> Here is my issue.
>
> I am using this formula to search a linked sheet to confirm that an
> account is older than 120 days and sum the balance on those accounts.
> Works great, except one problem as of tomorrow the number for the
> current date is no longer accurate therefore the 120 days isn't
> accurate.
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<38410",'[Account
> 2005.xls]June'!$C:$C)
>
> So then I came up with this solution:
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<(TODAY-120)",'[Account
> 2005.xls]June'!$C:$C)
>
> This gives me $0.00 everytime. I tested the today-120 formula by
> itself and it works fine. Not sure if the problem is the order of the
> operation or some sort of loop problem. If this is not the correct
> resolution does anyone have any ideas.
>
> I also did the date calculation in a seperate cell and did a formula to
> point at that cell i.e. <J2 but it didn't work either.
>
> Thanks for your help.
>
> Mike W.
> Nashville
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile: http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>
>

=SUMIF('[Account
2005.xls]June'!$A:$A,"<"&(TODAY()-120),'[Account2005.xls]June'!$C:$C)

--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> Here is my issue.
>
> I am using this formula to search a linked sheet to confirm that an
> account is older than 120 days and sum the balance on those accounts.
> Works great, except one problem as of tomorrow the number for the
> current date is no longer accurate therefore the 120 days isn't
> accurate.
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<38410",'[Account
> 2005.xls]June'!$C:$C)
>
> So then I came up with this solution:
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<(TODAY-120)",'[Account
> 2005.xls]June'!$C:$C)
>
> This gives me $0.00 everytime. I tested the today-120 formula by
> itself and it works fine. Not sure if the problem is the order of the
> operation or some sort of loop problem. If this is not the correct
> resolution does anyone have any ideas.
>
> I also did the date calculation in a seperate cell and did a formula to
> point at that cell i.e. <J2 but it didn't work either.
>
> Thanks for your help.
>
> Mike W.
> Nashville
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>

=SUMIF('[Account
2005.xls]June'!$A:$A,"<"&(TODAY()-120),'[Account2005.xls]June'!$C:$C)

--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> Here is my issue.
>
> I am using this formula to search a linked sheet to confirm that an
> account is older than 120 days and sum the balance on those accounts.
> Works great, except one problem as of tomorrow the number for the
> current date is no longer accurate therefore the 120 days isn't
> accurate.
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<38410",'[Account
> 2005.xls]June'!$C:$C)
>
> So then I came up with this solution:
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<(TODAY-120)",'[Account
> 2005.xls]June'!$C:$C)
>
> This gives me $0.00 everytime. I tested the today-120 formula by
> itself and it works fine. Not sure if the problem is the order of the
> operation or some sort of loop problem. If this is not the correct
> resolution does anyone have any ideas.
>
> I also did the date calculation in a seperate cell and did a formula to
> point at that cell i.e. <J2 but it didn't work either.
>
> Thanks for your help.
>
> Mike W.
> Nashville
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>

try
=SUMIF('[Account 2005.xls]June'!$A:$A,"<" & datevalue(today()) &
"-90",'[Account
2005.xls]June'!$C:$C)

"miwarren" wrote:

>
> Here is my issue.
>
> I am using this formula to search a linked sheet to confirm that an
> account is older than 120 days and sum the balance on those accounts.
> Works great, except one problem as of tomorrow the number for the
> current date is no longer accurate therefore the 120 days isn't
> accurate.
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<38410",'[Account
> 2005.xls]June'!$C:$C)
>
> So then I came up with this solution:
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<(TODAY-120)",'[Account
> 2005.xls]June'!$C:$C)
>
> This gives me $0.00 everytime. I tested the today-120 formula by
> itself and it works fine. Not sure if the problem is the order of the
> operation or some sort of loop problem. If this is not the correct
> resolution does anyone have any ideas.
>
> I also did the date calculation in a seperate cell and did a formula to
> point at that cell i.e. <J2 but it didn't work either.
>
> Thanks for your help.
>
> Mike W.
> Nashville
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile: http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>
>

Your solution is a bit drastic <G>

I should have realised that problem and given you a formula that would last
the closed workbooks. Try this instead

=SUMPRODUCT(--('[Account
2005.xls]June'!$A1:$A1000<"TODAY()-120),'[Account2005.xls]June'!$C1:$C1000)

--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> I did as you said Bob and it worked, *if* the workbook that contained
> the source was open. I had this problem earlier because it wouldn't
> pull the linked data unless you opened the source document. So I
> created a macro that would open and close all documents that were
> linked to the summary document therefore loading the linked content.
> That was the fix I was using and it worked with the first formula that
> I posted. Well, in the cells that I used the formula you suggested it
> would load the data as long as the source was open but as soon as the
> source was closed it went back to #VALUE!. ugh... Any suggestions???
> I really do appreciate your help and patience. It is starting to
> overwhelm me.
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>

Sorry, clumsiness. SUMPRODUCT only works on part of a column, not the whole
column, so try

=SUMPRODUCT('[Account
2005.xls]June'!$A1:$A1000,"<"&TODAY()-120,'[Account2005.xls]June'!$C1:$C1000
)

or howver many rows that you have.


--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> I entered it like this and now I get a #NUM! error message. Any other
> help you can provide?
>
> =SUMPRODUCT('[Account 2005.xls]June'!$A:$A,"<"&TODAY()-120,'[Account
> 2005.xls]June'!$C:$C)
>
> Thanks again!!!
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>

=SUMIF('[Account
2005.xls]June'!$A:$A,"<"&(TODAY()-120),'[Account2005.xls]June'!$C:$C)

--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> Here is my issue.
>
> I am using this formula to search a linked sheet to confirm that an
> account is older than 120 days and sum the balance on those accounts.
> Works great, except one problem as of tomorrow the number for the
> current date is no longer accurate therefore the 120 days isn't
> accurate.
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<38410",'[Account
> 2005.xls]June'!$C:$C)
>
> So then I came up with this solution:
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<(TODAY-120)",'[Account
> 2005.xls]June'!$C:$C)
>
> This gives me $0.00 everytime. I tested the today-120 formula by
> itself and it works fine. Not sure if the problem is the order of the
> operation or some sort of loop problem. If this is not the correct
> resolution does anyone have any ideas.
>
> I also did the date calculation in a seperate cell and did a formula to
> point at that cell i.e. <J2 but it didn't work either.
>
> Thanks for your help.
>
> Mike W.
> Nashville
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>

try
=SUMIF('[Account 2005.xls]June'!$A:$A,"<" & datevalue(today()) &
"-90",'[Account
2005.xls]June'!$C:$C)

"miwarren" wrote:

>
> Here is my issue.
>
> I am using this formula to search a linked sheet to confirm that an
> account is older than 120 days and sum the balance on those accounts.
> Works great, except one problem as of tomorrow the number for the
> current date is no longer accurate therefore the 120 days isn't
> accurate.
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<38410",'[Account
> 2005.xls]June'!$C:$C)
>
> So then I came up with this solution:
>
> =SUMIF('[Account 2005.xls]June'!$A:$A,"<(TODAY-120)",'[Account
> 2005.xls]June'!$C:$C)
>
> This gives me $0.00 everytime. I tested the today-120 formula by
> itself and it works fine. Not sure if the problem is the order of the
> operation or some sort of loop problem. If this is not the correct
> resolution does anyone have any ideas.
>
> I also did the date calculation in a seperate cell and did a formula to
> point at that cell i.e. <J2 but it didn't work either.
>
> Thanks for your help.
>
> Mike W.
> Nashville
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile: http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>
>

Your solution is a bit drastic <G>

I should have realised that problem and given you a formula that would last
the closed workbooks. Try this instead

=SUMPRODUCT(--('[Account
2005.xls]June'!$A1:$A1000<"TODAY()-120),'[Account2005.xls]June'!$C1:$C1000)

--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> I did as you said Bob and it worked, *if* the workbook that contained
> the source was open. I had this problem earlier because it wouldn't
> pull the linked data unless you opened the source document. So I
> created a macro that would open and close all documents that were
> linked to the summary document therefore loading the linked content.
> That was the fix I was using and it worked with the first formula that
> I posted. Well, in the cells that I used the formula you suggested it
> would load the data as long as the source was open but as soon as the
> source was closed it went back to #VALUE!. ugh... Any suggestions???
> I really do appreciate your help and patience. It is starting to
> overwhelm me.
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>

Sorry, clumsiness. SUMPRODUCT only works on part of a column, not the whole
column, so try

=SUMPRODUCT('[Account
2005.xls]June'!$A1:$A1000,"<"&TODAY()-120,'[Account2005.xls]June'!$C1:$C1000
)

or howver many rows that you have.


--
HTH

Bob Phillips

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> I entered it like this and now I get a #NUM! error message. Any other
> help you can provide?
>
> =SUMPRODUCT('[Account 2005.xls]June'!$A:$A,"<"&TODAY()-120,'[Account
> 2005.xls]June'!$C:$C)
>
> Thanks again!!!
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=382550
>