I've been stuck with a problem here and I can't seem to come up with a
solution.

{=SUM(IF((Sell!I$3:I$13=B7),Sell!E$3:E$13,0))}

To sum up what I'm trying to do, I have one sheet to track sales on a
day to day basis. The above code is a cell that finds the total
profits for that particular day, which is marked in B7. It does this
by going to the "Sell" worksheet, which has the profit for each
individual sale, along with the day it was made. So basically the code
up there looks at the date of the sale (I column), and then looks for
the profit in that row (E column), and adds them all up. So far this
has been working fine.

My problem is that the data on the "Sell" worksheet will be constantly
growing. Right now it only goes down to the 13th row, so that's what I
used in the code up there. However, more will be added in the future,
and I want to be able to automatically update all the forumulas like
the one above to cover the entire area. At first I tried making the
range go all the way to 65k+ rows, but this made it run horribly slow.

A possible solution I had was to somehow return a row value (the last
row of data from the "Sell" worksheet) to the equation, and use that in
the bolded places:

{=SUM(IF((Sell!I$3:I$*13*=B7),Sell!E$3:E$*13*,0))}

I figured out how to make a cell that returns the row number of the
last row of data on the "Sell" worksheet. What I couldn't figure out
was how to make the formula utilize this row number value. If this is
possible, what is the syntax? If it's not possible, does anyone else
have any ideas?

I think that covers everything. If you have any further questions
please ask, this is becoming hopelessly confusing to me, so it's
possible I could have made some parts unclear.


--
EdJ
------------------------------------------------------------------------
EdJ's Profile: http://www.excelforum.com/member.php...o&userid=26668
View this thread: http://www.excelforum.com/showthread...hreadid=399434

One option would be to use dynamic ranges...

Insert Name Define

Name: Date

Refers to:
=Sell!$I$3:INDEX(Sell!$I$3:$I$65536,MATCH(9.999999 99999999E+307,Sell!$I$3:$I$65536))

Click Add

Name: Profit

Refers to:
=Sell!$E$3:INDEX(Sell!$E$3:$E$65536,MATCH(9.999999 99999999E+307,Sell!$I$3:$I$65536))

Click Ok

Then simply use the following formula...

=SUM(IF(Date=B7,Profit))

...confirmed with CONTROL+SHIFT+ENTER. Note that I've assumed that
Column I contains true date values. If Column I actually contains text
values, replace...

9.99999999999999E+307

with

REPT("z",255)

Hope this helps!

EdJ Wrote:
I've been stuck with a problem here and I can't seem to come up with a
solution.

{=SUM(IF((Sell!I$3:I$13=B7),Sell!E$3:E$13,0))}

To sum up what I'm trying to do, I have one sheet to track sales on a
day to day basis. The above code is a cell that finds the total
profits for that particular day, which is marked in B7. It does this
by going to the "Sell" worksheet, which has the profit for each
individual sale, along with the day it was made. So basically the code
up there looks at the date of the sale (I column), and then looks for
the profit in that row (E column), and adds them all up. So far this
has been working fine.

My problem is that the data on the "Sell" worksheet will be constantly
growing. Right now it only goes down to the 13th row, so that's what I
used in the code up there. However, more will be added in the future,
and I want to be able to automatically update all the forumulas like
the one above to cover the entire area. At first I tried making the
range go all the way to 65k+ rows, but this made it run horribly slow.

A possible solution I had was to somehow return a row value (the last
row of data from the "Sell" worksheet) to the equation, and use that in
the bolded places:

{=SUM(IF((Sell!I$3:I$*13*=B7),Sell!E$3:E$*13*,0))}

I figured out how to make a cell that returns the row number of the
last row of data on the "Sell" worksheet. What I couldn't figure out
was how to make the formula utilize this row number value. If this is
possible, what is the syntax? If it's not possible, does anyone else
have any ideas?

I think that covers everything. If you have any further questions
please ask, this is becoming hopelessly confusing to me, so it's
possible I could have made some parts unclear.


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=399434

Try this one, it shouldn't slow you down very much...

=SumIf(Sell!$I$3:$I$65000,B7,Sell!$E$3:$E$65000)

"EdJ" wrote:


I've been stuck with a problem here and I can't seem to come up with a
solution.

{=SUM(IF((Sell!I$3:I$13=B7),Sell!E$3:E$13,0))}

To sum up what I'm trying to do, I have one sheet to track sales on a
day to day basis. The above code is a cell that finds the total
profits for that particular day, which is marked in B7. It does this
by going to the "Sell" worksheet, which has the profit for each
individual sale, along with the day it was made. So basically the code
up there looks at the date of the sale (I column), and then looks for
the profit in that row (E column), and adds them all up. So far this
has been working fine.

My problem is that the data on the "Sell" worksheet will be constantly
growing. Right now it only goes down to the 13th row, so that's what I
used in the code up there. However, more will be added in the future,
and I want to be able to automatically update all the forumulas like
the one above to cover the entire area. At first I tried making the
range go all the way to 65k+ rows, but this made it run horribly slow.

A possible solution I had was to somehow return a row value (the last
row of data from the "Sell" worksheet) to the equation, and use that in
the bolded places:

{=SUM(IF((Sell!I$3:I$*13*=B7),Sell!E$3:E$*13*,0))}

I figured out how to make a cell that returns the row number of the
last row of data on the "Sell" worksheet. What I couldn't figure out
was how to make the formula utilize this row number value. If this is
possible, what is the syntax? If it's not possible, does anyone else
have any ideas?

I think that covers everything. If you have any further questions
please ask, this is becoming hopelessly confusing to me, so it's
possible I could have made some parts unclear.


--
EdJ
------------------------------------------------------------------------
EdJ's Profile: http://www.excelforum.com/member.php...o&userid=26668
View this thread: http://www.excelforum.com/showthread...hreadid=399434