I have a spreadsheet of workorders showing various fields.
In the report i can count the amount of jobs overdue by 30 days. I can do a
count of how many jobs are priority 1. However i don't know how to right a
formula that is going tell me how many priority one jobs are overdue by 30
days. It seems easy but I don't know.

For eg. Priority ......... Age
1 23
1 35
I have attempted a few formula such as
=sumif(sheet!$:$,1,sheet!$:$)-countif(sheet!$:$,"30") something like that.

Hi,

Try this:

=SUMRPODUCT((A1:A10=1)*(B1:B1030))

Regards,
KL


"Visual" wrote in message
...
I have a spreadsheet of workorders showing various fields.
In the report i can count the amount of jobs overdue by 30 days. I can do
a
count of how many jobs are priority 1. However i don't know how to right
a
formula that is going tell me how many priority one jobs are overdue by 30
days. It seems easy but I don't know.

For eg. Priority ......... Age
1 23
1 35
I have attempted a few formula such as
=sumif(sheet!$:$,1,sheet!$:$)-countif(sheet!$:$,"30") something like
that.

On Sun, 17 Jul 2005 23:29:02 -0700, Visual
wrote:
I have a spreadsheet of workorders showing various fields.
In the report i can count the amount of jobs overdue by 30 days. I can do a
count of how many jobs are priority 1. However i don't know how to right a
formula that is going tell me how many priority one jobs are overdue by 30
days. It seems easy but I don't know.

For eg. Priority ......... Age
1 23
1 35
I have attempted a few formula such as
=sumif(sheet!$:$,1,sheet!$:$)-countif(sheet!$:$,"30") something like that.

Use an array formula.

(Assuming that your priorities are in A1:A3 and your ages are in
B1:B3)

Enter: =SUM(IF(A1:A3=1,IF(B1:B329,1,0),0))

And confirm by pressing Ctrl+Shift+Enter
--
"Learning is a behavior that results from consequences."
B.F. Skinner

....another array-entered formula option:

=SUM((A1:A3=1)*(B1:B329))

Regards,
KL


"Mangus Pyke" wrote in message
...
On Sun, 17 Jul 2005 23:29:02 -0700, Visual
wrote:
I have a spreadsheet of workorders showing various fields.
In the report i can count the amount of jobs overdue by 30 days. I can do
a
count of how many jobs are priority 1. However i don't know how to right
a
formula that is going tell me how many priority one jobs are overdue by 30
days. It seems easy but I don't know.

For eg. Priority ......... Age
1 23
1 35
I have attempted a few formula such as
=sumif(sheet!$:$,1,sheet!$:$)-countif(sheet!$:$,"30") something like
that.

Use an array formula.

(Assuming that your priorities are in A1:A3 and your ages are in
B1:B3)

Enter: =SUM(IF(A1:A3=1,IF(B1:B329,1,0),0))

And confirm by pressing Ctrl+Shift+Enter
--
"Learning is a behavior that results from consequences."
B.F. Skinner

Countif only takes one criterium. To use several criteria, you can use the
sumproduct( ) function.
= sumproduct( ($A$2:$A$100=1) * ($B$1:$B$B10030) * 1)
-- sums 1 for all cells of A1:A100 equals to 1 AND (*) all matching cells
of B1:B100 greater than 30.
You can add as many criteria as you want.
--
Regards,
Sébastien


"Visual" wrote:

I have a spreadsheet of workorders showing various fields.
In the report i can count the amount of jobs overdue by 30 days. I can do a
count of how many jobs are priority 1. However i don't know how to right a
formula that is going tell me how many priority one jobs are overdue by 30
days. It seems easy but I don't know.

For eg. Priority ......... Age
1 23
1 35
I have attempted a few formula such as
=sumif(sheet!$:$,1,sheet!$:$)-countif(sheet!$:$,"30") something like that.

sorry, I meant =SUMRPODUCT((A1:A10=1)*(B1:B1029))

KL

"KL" wrote in message
...
Hi,

Try this:

=SUMRPODUCT((A1:A10=1)*(B1:B1030))

Regards,
KL


"Visual" wrote in message
...
I have a spreadsheet of workorders showing various fields.
In the report i can count the amount of jobs overdue by 30 days. I can
do a
count of how many jobs are priority 1. However i don't know how to right
a
formula that is going tell me how many priority one jobs are overdue by
30
days. It seems easy but I don't know.

For eg. Priority ......... Age
1 23
1 35
I have attempted a few formula such as
=sumif(sheet!$:$,1,sheet!$:$)-countif(sheet!$:$,"30") something like
that.

Hi sebastienm,


"sebastienm" wrote
= sumproduct( ($A$2:$A$100=1) * ($B$1:$B$B10030) * 1)

your *1 is redundant as the two equations return arrays of TRUE/FALSE and
those when multiplied by each other are coerced to 1/0. Thus SUMPRODUCT sums
up an array of 1s and 0s.

Regards,
KL

In some situation, it is necessary. Not in this particular case, that's true,
but i prefer to keep the '* 1' so that i don't spend too much time searching
the source of a 'wrong' result.

Eg: Say you have a list of strings, dates, numbers,... in A1:Ax
=SUMPRODUCT((LEN(A4:A7)3))
--- returns 0 whether or not the text in a cell has a length greater
than 3
Now try
=SUMPRODUCT((LEN(A4:A7)3) * 1)
--- here, you get the right result.
--
Regards,
Sébastien


"KL" wrote:

Hi sebastienm,


"sebastienm" wrote
= sumproduct( ($A$2:$A$100=1) * ($B$1:$B$B10030) * 1)

your *1 is redundant as the two equations return arrays of TRUE/FALSE and
those when multiplied by each other are coerced to 1/0. Thus SUMPRODUCT sums
up an array of 1s and 0s.

Regards,
KL

Hi sebastienm,

but i prefer to keep the '* 1' so that i don't spend too much time
searching
the source of a 'wrong' result.

I hope you are also aware what is the price you are paying for this. Just
imagine that, in the original formula you proposed, instead of range B1:B100
you work with B1:B1000 (which I think is more realistic). So by adding *1 to
your formula you effectively add 1000 additional (unnecessary) operations as
each member of the array will be multiplied by 1. Now a few what-if's: 1.
there are 10,000 rows involved, 2. there are more than one instance of the
formula, 3. there are more than one sheet with several instances of the
formula involving thousands of cells?

=SUMPRODUCT((LEN(A4:A7)3) * 1)
--- here, you get the right result.

Agree, this can also be achieved by double minus:

=SUMPRODUCT(--(LEN(A4:A7)3))

Regards,
KL

Sure, there's a price to pay, but even with 50k rows, it's still very
negligeable in 99.9% of my situations compare to spending even 10 minutes to
figure out that somewhere in the model there is a error and where it comes
from. Or maybe more than that to the person who will have to modify my model
later on and may encounter issues.
After thinking about it, there might not even be a price to this situation
'* 1'. If i remember correctly, Excel compiles functions as they are entered;
so with '* 1' there may not even be a computation of the *1, only a forced
cast.
Now i have just tested in xl 2002, =SUMPRODUCT((A1:A133)) when all the
cells are numbers formatted as numbers. It returns 0 without the *1 even
though some values are greater than 3.

Just personal preference... my priorities are more ... keeping my models
easy to debug and optimize by first avoiding volatile functions, avoiding
regular Lookup functions which recomputes when any cell in the source range
changes (even though it is not the first column, nor the column used for the
returned value), order my sheets in alphabetical order to prevent
double-computations in XL2k....
I think it's not always practical to optimize a model on all aspects. Eg: i
wrote some of my usual custom functions in C, but most of the time, i need
something quick, so i code a function in vba, even though it's going to
compute 20 times slower than in C... in the end it's still a fraction of a
second of computation (or say 1 second) compared to the time it takes to
writting/testing/modifying/accessing it with C.
Just personal preference :-)

--
Regards,
Sébastien


"KL" wrote:

Hi sebastienm,

but i prefer to keep the '* 1' so that i don't spend too much time
searching
the source of a 'wrong' result.

I hope you are also aware what is the price you are paying for this. Just
imagine that, in the original formula you proposed, instead of range B1:B100
you work with B1:B1000 (which I think is more realistic). So by adding *1 to
your formula you effectively add 1000 additional (unnecessary) operations as
each member of the array will be multiplied by 1. Now a few what-if's: 1.
there are 10,000 rows involved, 2. there are more than one instance of the
formula, 3. there are more than one sheet with several instances of the
formula involving thousands of cells?

=SUMPRODUCT((LEN(A4:A7)3) * 1)
--- here, you get the right result.

Agree, this can also be achieved by double minus:

=SUMPRODUCT(--(LEN(A4:A7)3))

Regards,
KL

Hi sebastienm,

"sebastienm"
Sure, there's a price to pay, but even with 50k rows, it's still very
negligeable in 99.9% of my situations compare to spending even 10 minutes
to
figure out that somewhere in the model there is a error and where it comes
from. Or maybe more than that to the person who will have to modify my
model
later on and may encounter issues.

Once again, I hope that except for your very first argument (...99.9%...)
you don't believe in the rest of them as otherwise I would like to ask you a
couple of questions (if you don't mind of course)

1) how exactly does *1 enable you to identify/prevent errors in your
formula?
2) if you claim to unnderstand the way SUMPRODUCT works how come it takes
you 10 min to spot a matematical operator between two conditions, which is
what it is all about? Isn't it easier to remember that any matematical
operator coerces TRUE/FALSE to 1/0 (e.g. *,-,+,^,/) and therefore there is
no need to use *1,+0,-0,^1,/1,--, etc.

As a side-note: my tests with the two formulas (with the 50,000 rows range,
100 instances and with actual data) suggest that there is a 17%
recalculation time difference (12 sec vs 14 sec)

After thinking about it, there might not even be a price to this situation
'* 1'. If i remember correctly, Excel compiles functions as they are
entered;
so with '* 1' there may not even be a computation of the *1, only a forced
cast.

Full sheet recalc occurs at a number of events so there is necessarily a
recalc of all operations in the formula at least when you open the file
(unless you set calculation to manual).

Now i have just tested in xl 2002, =SUMPRODUCT((A1:A133)) when all the
cells are numbers formatted as numbers. It returns 0 without the *1 even
though some values are greater than 3.

The format has nothing to do with this issue, I think you are just looking
in the wrong place. In order to understand that =SUMPRODUCT((A1:A133)) can
not possibly return anything rather than 0 it takes just undestanding that
SUMPRODUCT can not sum the logical values TRUE and FALSE. And thus you have
to coerce them explicitly to 1 and 0. Try to input each of the two formulas
in a cell, select everything that is inside the SUMPRODUCT, press F9 and
compare the two arrays.

Just personal preference... my priorities are more ... keeping my models
easy to debug and optimize by first avoiding volatile functions, avoiding
regular Lookup functions which recomputes when any cell in the source
range
changes (even though it is not the first column, nor the column used for
the
returned value), order my sheets in alphabetical order to prevent
double-computations in XL2k....

this all sounds very dogmatic to me and is also outside the scope of this
conversation I guess.

Regards,
KL

Just a precision before starting here; i was just discussing my point of view
in my situations, that's it; not saying that's the way things are, just
sharing observations, and not claiming that i KNOW how things work... far
from that.
So if the lack of emoticons, my english vocabulary and my way of putting
sentences in English (which i though was quite ok) seemed misplaced or rude,
i apologize.

Now, that said...

1)how exactly does *1 enable you to identify/prevent errors in your formula?
By using *1, in my mind, i know that the goal is to Count; just easier to
me when debugging with many conditions in the expression. But to be honest, i
also thought it used to be necessary in some specific situations. I cannot
find any right now though, so it is possible that i learned something wrong
someday long ago.
Anyway, when distributing the book, i think it's easier for other
users/owners to figure out what is going on, but well, seems like it wouldn't
be an issue for you.
2) takes 10mns...between two conditions
First of all, to make things clear, i don't claim to know how the function
works. Now, i was not speaking of 'between two conditions' but when debugging
a model in general. Depending of the size of a model, it is not necessarily
obvious to pinpoint issues within the minute. Even less when you are given a
book that someone else has built; personaly i have to deal with foreign books
almost everyday at work.
3) sheet recalc.
It has nothing to do with sheet recalc or auto/manual setting. If excel
compiles expressions once when entered ...[can anybody can confirm? i'd like
to know.]. Some compilers tend to remove extra unnecessary stuff: compiling
'number*1' to 'number, 'number+0' to 'number..... Not sure how excel's
compiler works (this is why i said 'might not even be'), but i would think it
is intelligent enough to compile 'number * 1' to 'number'.
4) The format has nothing to do with this issue
hmmm... that's what i was trying to say ... everything being numbers meaning
that the cause of the different results wouldn't come from formats for sure
.... it was just to describe the situation.
5)dogmatic
.... maybe ... in some ways...

At least, i've learned that there seems to be no exception to the coersion.

--
Regards,
Sébastien


"KL" wrote:

Hi sebastienm,

"sebastienm"
Sure, there's a price to pay, but even with 50k rows, it's still very
negligeable in 99.9% of my situations compare to spending even 10 minutes
to
figure out that somewhere in the model there is a error and where it comes
from. Or maybe more than that to the person who will have to modify my
model
later on and may encounter issues.

Once again, I hope that except for your very first argument (...99.9%...)
you don't believe in the rest of them as otherwise I would like to ask you a
couple of questions (if you don't mind of course)

1) how exactly does *1 enable you to identify/prevent errors in your
formula?
2) if you claim to unnderstand the way SUMPRODUCT works how come it takes
you 10 min to spot a matematical operator between two conditions, which is
what it is all about? Isn't it easier to remember that any matematical
operator coerces TRUE/FALSE to 1/0 (e.g. *,-,+,^,/) and therefore there is
no need to use *1,+0,-0,^1,/1,--, etc.

As a side-note: my tests with the two formulas (with the 50,000 rows range,
100 instances and with actual data) suggest that there is a 17%
recalculation time difference (12 sec vs 14 sec)

After thinking about it, S price to this situation
'* 1'. If i remember correctly, Excel compiles functions as they are
entered;
so with '* 1' there may not even be a computation of the *1, only a forced
cast.

Full sheet recalc occurs at a number of events so there is necessarily a
recalc of all operations in the formula at least when you open the file
(unless you set calculation to manual).

Now i have just tested in xl 2002, =SUMPRODUCT((A1:A133)) when all the
cells are numbers formatted as numbers. It returns 0 without the *1 even
though some values are greater than 3.

The format has nothing to do with this issue, I think you are just looking
in the wrong place. In order to understand that =SUMPRODUCT((A1:A133)) can
not possibly return anything rather than 0 it takes just undestanding that
SUMPRODUCT can not sum the logical values TRUE and FALSE. And thus you have
to coerce them explicitly to 1 and 0. Try to input each of the two formulas
in a cell, select everything that is inside the SUMPRODUCT, press F9 and
compare the two arrays.

Just personal preference... my priorities are more ... keeping my models
easy to debug and optimize by first avoiding volatile functions, avoiding
regular Lookup functions which recomputes when any cell in the source
range
changes (even though it is not the first column, nor the column used for
the
returned value), order my sheets in alphabetical order to prevent
double-computations in XL2k....

this all sounds very dogmatic to me and is also outside the scope of this
conversation I guess.

Regards,
KL

Hi

=SUM(IF(Mackay!E2:E5000=1,IF(Mackay!L2:L500030,1, 0),0)) I entered this
formula and it didn't work it returns a value of 0. I checked and yes its
not meant to be zero.

"Mangus Pyke" wrote:

On Sun, 17 Jul 2005 23:29:02 -0700, Visual
wrote:
I have a spreadsheet of workorders showing various fields.
In the report i can count the amount of jobs overdue by 30 days. I can do a
count of how many jobs are priority 1. However i don't know how to right a
formula that is going tell me how many priority one jobs are overdue by 30
days. It seems easy but I don't know.

For eg. Priority ......... Age
1 23
1 35
I have attempted a few formula such as
=sumif(sheet!$:$,1,sheet!$:$)-countif(sheet!$:$,"30") something like that.

Use an array formula.

(Assuming that your priorities are in A1:A3 and your ages are in
B1:B3)

Enter: =SUM(IF(A1:A3=1,IF(B1:B329,1,0),0))

And confirm by pressing Ctrl+Shift+Enter
--
"Learning is a behavior that results from consequences."
B.F. Skinner

It worked. What is it with the row selection. Why cant u have A:A u need
A1:A1000. Interesting

"KL" wrote:

sorry, I meant =SUMRPODUCT((A1:A10=1)*(B1:B1029))

KL

"KL" wrote in message
...
Hi,

Try this:

=SUMRPODUCT((A1:A10=1)*(B1:B1030))

Regards,
KL


"Visual" wrote in message
...
I have a spreadsheet of workorders showing various fields.
In the report i can count the amount of jobs overdue by 30 days. I can
do a
count of how many jobs are priority 1. However i don't know how to right
a
formula that is going tell me how many priority one jobs are overdue by
30
days. It seems easy but I don't know.

For eg. Priority ......... Age
1 23
1 35
I have attempted a few formula such as
=sumif(sheet!$:$,1,sheet!$:$)-countif(sheet!$:$,"30") something like
that.

Sorry just another quick one.

=SUMPRODUCT((Mackay!$E$1:$E$5000=1)*(Mackay!$L$1:$ L$500030)*(Macakay!$L$1:$L$5000<60)) how would I make this one work. Jobs between 30 and 60 days

Thanks.

"Visual" wrote:

It worked. What is it with the row selection. Why cant u have A:A u need
A1:A1000. Interesting

"KL" wrote:

sorry, I meant =SUMRPODUCT((A1:A10=1)*(B1:B1029))

KL

"KL" wrote in message
...
Hi,

Try this:

=SUMRPODUCT((A1:A10=1)*(B1:B1030))

Regards,
KL


"Visual" wrote in message
...
I have a spreadsheet of workorders showing various fields.
In the report i can count the amount of jobs overdue by 30 days. I can
do a
count of how many jobs are priority 1. However i don't know how to right
a
formula that is going tell me how many priority one jobs are overdue by
30
days. It seems easy but I don't know.

For eg. Priority ......... Age
1 23
1 35
I have attempted a few formula such as
=sumif(sheet!$:$,1,sheet!$:$)-countif(sheet!$:$,"30") something like
that.

This will work - you just have a spelling error in your final expression
MACAKAY instead of MACKAY

--
Regards
Roger Govier
"Visual" wrote in message
...
Sorry just another quick one.

=SUMPRODUCT((Mackay!$E$1:$E$5000=1)*(Mackay!$L$1:$ L$500030)*(Macakay!$L$1:$L$5000<60))
how would I make this one work. Jobs between 30 and 60 days

Thanks.

"Visual" wrote:

It worked. What is it with the row selection. Why cant u have A:A u
need
A1:A1000. Interesting

"KL" wrote:

sorry, I meant =SUMRPODUCT((A1:A10=1)*(B1:B1029))

KL

"KL" wrote in message
...
Hi,

Try this:

=SUMRPODUCT((A1:A10=1)*(B1:B1030))

Regards,
KL


"Visual" wrote in message
...
I have a spreadsheet of workorders showing various fields.
In the report i can count the amount of jobs overdue by 30 days. I
can
do a
count of how many jobs are priority 1. However i don't know how to
right
a
formula that is going tell me how many priority one jobs are overdue
by
30
days. It seems easy but I don't know.

For eg. Priority ......... Age
1 23
1 35
I have attempted a few formula such as
=sumif(sheet!$:$,1,sheet!$:$)-countif(sheet!$:$,"30") something
like
that.

Hi Visual,

This is an array formula which means that it needs to be confirmed by
CTRL+SHIFT+ENTER (not just ENTER) so your formula should appear between
curly brackets {}.

Regards,
KL

"Visual" wrote in message
...
Hi

=SUM(IF(Mackay!E2:E5000=1,IF(Mackay!L2:L500030,1, 0),0)) I entered this
formula and it didn't work it returns a value of 0. I checked and yes its
not meant to be zero.

"Mangus Pyke" wrote:

On Sun, 17 Jul 2005 23:29:02 -0700, Visual
wrote:
I have a spreadsheet of workorders showing various fields.
In the report i can count the amount of jobs overdue by 30 days. I can
do a
count of how many jobs are priority 1. However i don't know how to
right a
formula that is going tell me how many priority one jobs are overdue by
30
days. It seems easy but I don't know.

For eg. Priority ......... Age
1 23
1 35
I have attempted a few formula such as
=sumif(sheet!$:$,1,sheet!$:$)-countif(sheet!$:$,"30") something like
that.

Use an array formula.

(Assuming that your priorities are in A1:A3 and your ages are in
B1:B3)

Enter: =SUM(IF(A1:A3=1,IF(B1:B329,1,0),0))

And confirm by pressing Ctrl+Shift+Enter
--
"Learning is a behavior that results from consequences."
B.F. Skinner

Oops. Cheers to everyone who lent a hand.

"Roger Govier" wrote:

This will work - you just have a spelling error in your final expression
MACAKAY instead of MACKAY

--
Regards
Roger Govier
"Visual" wrote in message
...
Sorry just another quick one.

=SUMPRODUCT((Mackay!$E$1:$E$5000=1)*(Mackay!$L$1:$ L$500030)*(Macakay!$L$1:$L$5000<60))
how would I make this one work. Jobs between 30 and 60 days

Thanks.

"Visual" wrote:

It worked. What is it with the row selection. Why cant u have A:A u
need
A1:A1000. Interesting

"KL" wrote:

sorry, I meant =SUMRPODUCT((A1:A10=1)*(B1:B1029))

KL

"KL" wrote in message
...
Hi,

Try this:

=SUMRPODUCT((A1:A10=1)*(B1:B1030))

Regards,
KL


"Visual" wrote in message
...
I have a spreadsheet of workorders showing various fields.
In the report i can count the amount of jobs overdue by 30 days. I
can
do a
count of how many jobs are priority 1. However i don't know how to
right
a
formula that is going tell me how many priority one jobs are overdue
by
30
days. It seems easy but I don't know.

For eg. Priority ......... Age
1 23
1 35
I have attempted a few formula such as
=sumif(sheet!$:$,1,sheet!$:$)-countif(sheet!$:$,"30") something
like
that.

Hi,

I'm trying this formula but it throws a #NAME? error.

=SUMRPRODUCT(('North America'!O2:O1090TODAY)*('North
America'!H2:H1090="Single User"))

Anyone have any idea where the problem might be?

Thanks,
D


--
dungeonboy
------------------------------------------------------------------------
dungeonboy's Profile: http://www.excelforum.com/member.php...o&userid=26140
View this thread: http://www.excelforum.com/showthread...hreadid=387925

Woops. I'm a dumbass. I figured out my folly.


--
dungeonboy
------------------------------------------------------------------------
dungeonboy's Profile: http://www.excelforum.com/member.php...o&userid=26140
View this thread: http://www.excelforum.com/showthread...hreadid=387925