Need Help With the SUMPRODUCT Formula !!!!!!!!!!!!!!!!!!

A

Ayo

=SUMPRODUCT(('Sites Task List'!$B$2:$B$5000=$C4)*('Sites Task
List'!$K$2:$K$5000<>"Completed")*('Sites Task
List'!$L$2:$L$5000<>"Completed")*('Sites Task
List'!$R$2:$R$5000<>{"","Completed"})*('Sites Task
List'!$S$2:$S$5000<>{"","Completed"})*('Sites Task
List'!$T$2:$T$5000<>{"","Completed"})*('Sites Task
List'!$U$2:$U$5000<>{"","Completed"}))

What I am trying to calculate in the above formular is this:
If Columns K and L: Values are not equal to "Completed" then count the cells
in
Columns R, S, T, & U:
where values are NOTBLANK and are not equal to "Completed".
For example if R <>"" or "Completed" but S, T, and U are I want to count
that row, or
if R and S are <>"" or "Completed" but T, and U are I want to count that row

and so on.
 
K

ker_01

If I understand correctly, only for rows that are not complete (K and L) you
want to count that row (+1) if any of the four following cells are not blank
or incomplete (R,S,T,U)?

Untested, but this may give you some ideas;

=SUMPRODUCT(
('Sites Task List'!$B$2:$B$5000=$C4)*
('Sites Task List'!$K$2:$K$5000<>"Completed")*
('Sites Task List'!$L$2:$L$5000<>"Completed")*

AND(('Sites Task List'!$R$2:$R$5000<>""),
('Sites Task List'!$R$2:$R$5000<>"Completed"))*
OR
AND('Sites Task List'!$S$2:$S$5000<>""),
('Sites Task List'!$S$2:$S$5000<>"Completed")*
OR
[etc]
 
A

Ayo

Thanks. I will try it.

ker_01 said:
If I understand correctly, only for rows that are not complete (K and L) you
want to count that row (+1) if any of the four following cells are not blank
or incomplete (R,S,T,U)?

Untested, but this may give you some ideas;

=SUMPRODUCT(
('Sites Task List'!$B$2:$B$5000=$C4)*
('Sites Task List'!$K$2:$K$5000<>"Completed")*
('Sites Task List'!$L$2:$L$5000<>"Completed")*

AND(('Sites Task List'!$R$2:$R$5000<>""),
('Sites Task List'!$R$2:$R$5000<>"Completed"))*
OR
AND('Sites Task List'!$S$2:$S$5000<>""),
('Sites Task List'!$S$2:$S$5000<>"Completed")*
OR
[etc]

Ayo said:
=SUMPRODUCT(('Sites Task List'!$B$2:$B$5000=$C4)*('Sites Task
List'!$K$2:$K$5000<>"Completed")*('Sites Task
List'!$L$2:$L$5000<>"Completed")*('Sites Task
List'!$R$2:$R$5000<>{"","Completed"})*('Sites Task
List'!$S$2:$S$5000<>{"","Completed"})*('Sites Task
List'!$T$2:$T$5000<>{"","Completed"})*('Sites Task
List'!$U$2:$U$5000<>{"","Completed"}))

What I am trying to calculate in the above formular is this:
If Columns K and L: Values are not equal to "Completed" then count the cells
in
Columns R, S, T, & U:
where values are NOTBLANK and are not equal to "Completed".
For example if R <>"" or "Completed" but S, T, and U are I want to count
that row, or
if R and S are <>"" or "Completed" but T, and U are I want to count that row

and so on.
 
A

Ayo

Did you mean:

AND(('Sites Task List'!$R$2:$R$5000<>""),('Sites Task
List'!$R$2:$R$5000<>"Completed"))*

OR(AND('Sites Task List'!$S$2:$S$5000<>""),
('Sites Task List'!$S$2:$S$5000<>"Completed")*

OR(AND('Sites Task List'!$T$2:$T$5000<>""),
('Sites Task List'!$T$2:$T$5000<>"Completed")*



ker_01 said:
If I understand correctly, only for rows that are not complete (K and L) you
want to count that row (+1) if any of the four following cells are not blank
or incomplete (R,S,T,U)?

Untested, but this may give you some ideas;

=SUMPRODUCT(
('Sites Task List'!$B$2:$B$5000=$C4)*
('Sites Task List'!$K$2:$K$5000<>"Completed")*
('Sites Task List'!$L$2:$L$5000<>"Completed")*

AND(('Sites Task List'!$R$2:$R$5000<>""),
('Sites Task List'!$R$2:$R$5000<>"Completed"))*
OR
AND('Sites Task List'!$S$2:$S$5000<>""),
('Sites Task List'!$S$2:$S$5000<>"Completed")*
OR
[etc]

Ayo said:
=SUMPRODUCT(('Sites Task List'!$B$2:$B$5000=$C4)*('Sites Task
List'!$K$2:$K$5000<>"Completed")*('Sites Task
List'!$L$2:$L$5000<>"Completed")*('Sites Task
List'!$R$2:$R$5000<>{"","Completed"})*('Sites Task
List'!$S$2:$S$5000<>{"","Completed"})*('Sites Task
List'!$T$2:$T$5000<>{"","Completed"})*('Sites Task
List'!$U$2:$U$5000<>{"","Completed"}))

What I am trying to calculate in the above formular is this:
If Columns K and L: Values are not equal to "Completed" then count the cells
in
Columns R, S, T, & U:
where values are NOTBLANK and are not equal to "Completed".
For example if R <>"" or "Completed" but S, T, and U are I want to count
that row, or
if R and S are <>"" or "Completed" but T, and U are I want to count that row

and so on.
 
K

ker_01

Logically yes, syntax no- sorry about that, I should have been more explicit.
Here is shorthand for your equation, with a letter for each evaluation you
have in your formula.

=SUMPRODUCT(a,b,c,OR(AND(d,e),AND(f,g),AND(h,i),AND(j,k)))

That way, if any of the four AND subconditions evaluate to true, it should
count that row as "+1" in your sumproduct.

HTH,
Keith

Ayo said:
Did you mean:

AND(('Sites Task List'!$R$2:$R$5000<>""),('Sites Task
List'!$R$2:$R$5000<>"Completed"))*

OR(AND('Sites Task List'!$S$2:$S$5000<>""),
('Sites Task List'!$S$2:$S$5000<>"Completed")*

OR(AND('Sites Task List'!$T$2:$T$5000<>""),
('Sites Task List'!$T$2:$T$5000<>"Completed")*



ker_01 said:
If I understand correctly, only for rows that are not complete (K and L) you
want to count that row (+1) if any of the four following cells are not blank
or incomplete (R,S,T,U)?

Untested, but this may give you some ideas;

=SUMPRODUCT(
('Sites Task List'!$B$2:$B$5000=$C4)*
('Sites Task List'!$K$2:$K$5000<>"Completed")*
('Sites Task List'!$L$2:$L$5000<>"Completed")*

AND(('Sites Task List'!$R$2:$R$5000<>""),
('Sites Task List'!$R$2:$R$5000<>"Completed"))*
OR
AND('Sites Task List'!$S$2:$S$5000<>""),
('Sites Task List'!$S$2:$S$5000<>"Completed")*
OR
[etc]

Ayo said:
=SUMPRODUCT(('Sites Task List'!$B$2:$B$5000=$C4)*('Sites Task
List'!$K$2:$K$5000<>"Completed")*('Sites Task
List'!$L$2:$L$5000<>"Completed")*('Sites Task
List'!$R$2:$R$5000<>{"","Completed"})*('Sites Task
List'!$S$2:$S$5000<>{"","Completed"})*('Sites Task
List'!$T$2:$T$5000<>{"","Completed"})*('Sites Task
List'!$U$2:$U$5000<>{"","Completed"}))

What I am trying to calculate in the above formular is this:
If Columns K and L: Values are not equal to "Completed" then count the cells
in
Columns R, S, T, & U:
where values are NOTBLANK and are not equal to "Completed".
For example if R <>"" or "Completed" but S, T, and U are I want to count
that row, or
if R and S are <>"" or "Completed" but T, and U are I want to count that row

and so on.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top