Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help With the SUMPRODUCT Formula !!!!!!!!!!!!!!!!!!
=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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help With the SUMPRODUCT Formula !!!!!!!!!!!!!!!!!!
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" wrote: =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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help With the SUMPRODUCT Formula !!!!!!!!!!!!!!!!!!
Thanks. I will try it.
"ker_01" wrote: 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" wrote: =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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help With the SUMPRODUCT Formula !!!!!!!!!!!!!!!!!!
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" wrote: 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" wrote: =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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help With the SUMPRODUCT Formula !!!!!!!!!!!!!!!!!!
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),AN D(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" wrote: 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" wrote: 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" wrote: =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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help in SUMPRODUCT formula... | Excel Worksheet Functions | |||
Nesting a sumproduct formula within a sumif formula. | Excel Discussion (Misc queries) | |||
sumproduct formula | Excel Discussion (Misc queries) | |||
Sumproduct formula help | Excel Programming | |||
sumproduct formula | Excel Worksheet Functions |