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

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

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

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, 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.

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.

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.

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,

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