Can the data be sorted into date order in B4:B1002?
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"chris46521" wrote
in message ...
I'm not sure exactly what you mean when you say, "...Then you could
replace SP with formulae than look at less cells. Which cell is that
in, and where is the next SP and what does it look like?" The formula,
=SUMPRODUCT(--($B$4:$B$1002<=B4),--($M$4:$M$1002="PROD"),--($O$4:$O$1002="O"
))
repeats all the way down two separate column from rows 4 to 1002. As you
can see, the only part of it that is dynamic is in red.
The formula looks at cloumn B to see if the date is less than or equal
to the date of the current row in column B (there may be multiple dates
that are the same). Then it looks at column M to see how PROD's are
listed for that date. Finally it looks at column O to see how many O's
are listed for the previous conditions. SP then returns the sum of
these where the conditions are met in all three columns. Was this what
you were asking? Thanks for your help!
Bob Phillips Wrote:
Give me an assist. As I previously asked ... Then you could replace SP
with
formulae than look at less cells. Which cell
is that in, and where is the next SP and what does it look like?
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"chris46521"
wrote
in message
...
Thanks for the info Bob. How would you suggest that I change SP? Is
there a more efficient way of using a different formula than SP?
Thanks!
Bob Phillips Wrote:
Who's Bill?
I think you should explore improving the SP before going to code.
Code
can
be better in some cases, but it is inherently slower than built-in
functionality, so you have to use a different technique to get the
speed
improvements. You can usually make a big difference with rewriting
the
formulae.
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"chris46521"
wrote
in message
...
Thanks for your help Bill. That improved the speed a little bit.
I'm
not
sure if SP is the cause of my sheet being so slow. I guess it
would
be
best to replace the sumproduct formulae with code. It would
follow
the
same logic as the SP formula:
=SUMPRODUCT(--($B$4:$B$1002<=B4),--($M$4:$M$1002="PROD"),--($O$4:$O$1002="O"
))
Would you know of a way that I might go about doing this? Thanks
again
for your help.
Bob Phillips Wrote:
Got my calculate and calculation back to front
Application.Calculation = xlCalculationManual
and
Application.Calculation = xlCalculationAutomatic
what about the formulae?
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing
direct)
--
chris46521
------------------------------------------------------------------------
chris46521's Profile:
http://www.excelforum.com/member.php...o&userid=35909
View this thread:
http://www.excelforum.com/showthread...hreadid=569999
--
chris46521
------------------------------------------------------------------------
chris46521's Profile:
http://www.excelforum.com/member.php...o&userid=35909
View this thread:
http://www.excelforum.com/showthread...hreadid=569999
--
chris46521
------------------------------------------------------------------------
chris46521's Profile:
http://www.excelforum.com/member.php...o&userid=35909
View this thread: http://www.excelforum.com/showthread...hreadid=569999