Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
array formula(s)- not working, need some help please (problem with an 'AND')
Column AA= numeric count of program participation
Column D= list of manager names (name repeats on each line) Column G= list of employees by manager (plus one blank cell for manager/self) Column J = exclusion criteria basically I'm trying to get a sum of how much participation has occurred in each manager's area, without including that manager's participation, and only for people without exclusion criteria- so I need to sum the values in AA once per manager, excluding rows where Column G is blank or Column J is not blank. I got as far as this first array formula, which works to sum up everything for that manager, but includes the manager's row, and sums regardless of exclusion criteria: 'using IF(AND(D3<D2,LEN(D3)0),<stuff,"") to only show the total once per manager name {=IF(AND(D3<D2,LEN(D3)0),SUM(IF(D$3:D$3000=D3,AA $3:AA$3000,0)),"") } However, I still need to only count rows with employee names, and exclude those that should be excluded, so I tried to add the exclusion criteria first, and can't get it working: {=IF(AND(D3<D2,LEN(D3)0),SUM(IF(AND(D$3:D$3000=D 3,J$3:J$3000=""),AA$3:AA$3 000,0)),"") } something about adding the AND formula makes my result zero, even when I know there should be some value returned. -- AND(D$3:D$3000=D3,J$3:J$3000="") I would think that this would check each row one at a time, for example, include AA5 in the sum only if D5=D3 /and/ J5 <"" but now I'm starting to think that isn't how it works.... I'd appreciate any clarification on how to build this type of multiple criteria into an array formula. Thanks! Keith -- The enclosed questions or comments are entirely mine and don't represent the thoughts, views, or policy of my employer. Any errors or omissions are my own. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
array formula(s)- not working, need some help please (problem with an 'AND')
Something along the lines of
=IF(AND(D3<D2,LEN(D3)0),SUMPRODUCT(--(D$3:D$3000=D3),--(J$3:J$3000=""),AA$ 3:AA$3000)),"") just add more conditions like --(range, test_against) This is not an array formula -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "KR" wrote in message ... Column AA= numeric count of program participation Column D= list of manager names (name repeats on each line) Column G= list of employees by manager (plus one blank cell for manager/self) Column J = exclusion criteria basically I'm trying to get a sum of how much participation has occurred in each manager's area, without including that manager's participation, and only for people without exclusion criteria- so I need to sum the values in AA once per manager, excluding rows where Column G is blank or Column J is not blank. I got as far as this first array formula, which works to sum up everything for that manager, but includes the manager's row, and sums regardless of exclusion criteria: 'using IF(AND(D3<D2,LEN(D3)0),<stuff,"") to only show the total once per manager name {=IF(AND(D3<D2,LEN(D3)0),SUM(IF(D$3:D$3000=D3,AA $3:AA$3000,0)),"") } However, I still need to only count rows with employee names, and exclude those that should be excluded, so I tried to add the exclusion criteria first, and can't get it working: {=IF(AND(D3<D2,LEN(D3)0),SUM(IF(AND(D$3:D$3000=D 3,J$3:J$3000=""),AA$3:AA$3 000,0)),"") } something about adding the AND formula makes my result zero, even when I know there should be some value returned. -- AND(D$3:D$3000=D3,J$3:J$3000="") I would think that this would check each row one at a time, for example, include AA5 in the sum only if D5=D3 /and/ J5 <"" but now I'm starting to think that isn't how it works.... I'd appreciate any clarification on how to build this type of multiple criteria into an array formula. Thanks! Keith -- The enclosed questions or comments are entirely mine and don't represent the thoughts, views, or policy of my employer. Any errors or omissions are my own. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
array formula(s)- not working, need some help please (problem with
You might want to consider using the sumproduct formula. Let's say your data
is in rows 1-100. Try this =SUMPRODUCT((A1:A100),--(D1:D100="Name"),--(G1:G100<""),--(J1:J100<"")) "KR" wrote: Column AA= numeric count of program participation Column D= list of manager names (name repeats on each line) Column G= list of employees by manager (plus one blank cell for manager/self) Column J = exclusion criteria basically I'm trying to get a sum of how much participation has occurred in each manager's area, without including that manager's participation, and only for people without exclusion criteria- so I need to sum the values in AA once per manager, excluding rows where Column G is blank or Column J is not blank. I got as far as this first array formula, which works to sum up everything for that manager, but includes the manager's row, and sums regardless of exclusion criteria: 'using IF(AND(D3<D2,LEN(D3)0),<stuff,"") to only show the total once per manager name {=IF(AND(D3<D2,LEN(D3)0),SUM(IF(D$3:D$3000=D3,AA $3:AA$3000,0)),"") } However, I still need to only count rows with employee names, and exclude those that should be excluded, so I tried to add the exclusion criteria first, and can't get it working: {=IF(AND(D3<D2,LEN(D3)0),SUM(IF(AND(D$3:D$3000=D 3,J$3:J$3000=""),AA$3:AA$3 000,0)),"") } something about adding the AND formula makes my result zero, even when I know there should be some value returned. -- AND(D$3:D$3000=D3,J$3:J$3000="") I would think that this would check each row one at a time, for example, include AA5 in the sum only if D5=D3 /and/ J5 <"" but now I'm starting to think that isn't how it works.... I'd appreciate any clarification on how to build this type of multiple criteria into an array formula. Thanks! Keith -- The enclosed questions or comments are entirely mine and don't represent the thoughts, views, or policy of my employer. Any errors or omissions are my own. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
array formula(s)- not working, need some help please (problem
Oops, the formula should be
=SUMPRODUCT((AA1:AA100),--(D1:D100="Name"),--(G1:G100<""),--(J1:J100<"")) "Barb Reinhardt" wrote: You might want to consider using the sumproduct formula. Let's say your data is in rows 1-100. Try this =SUMPRODUCT((A1:A100),--(D1:D100="Name"),--(G1:G100<""),--(J1:J100<"")) "KR" wrote: Column AA= numeric count of program participation Column D= list of manager names (name repeats on each line) Column G= list of employees by manager (plus one blank cell for manager/self) Column J = exclusion criteria basically I'm trying to get a sum of how much participation has occurred in each manager's area, without including that manager's participation, and only for people without exclusion criteria- so I need to sum the values in AA once per manager, excluding rows where Column G is blank or Column J is not blank. I got as far as this first array formula, which works to sum up everything for that manager, but includes the manager's row, and sums regardless of exclusion criteria: 'using IF(AND(D3<D2,LEN(D3)0),<stuff,"") to only show the total once per manager name {=IF(AND(D3<D2,LEN(D3)0),SUM(IF(D$3:D$3000=D3,AA $3:AA$3000,0)),"") } However, I still need to only count rows with employee names, and exclude those that should be excluded, so I tried to add the exclusion criteria first, and can't get it working: {=IF(AND(D3<D2,LEN(D3)0),SUM(IF(AND(D$3:D$3000=D 3,J$3:J$3000=""),AA$3:AA$3 000,0)),"") } something about adding the AND formula makes my result zero, even when I know there should be some value returned. -- AND(D$3:D$3000=D3,J$3:J$3000="") I would think that this would check each row one at a time, for example, include AA5 in the sum only if D5=D3 /and/ J5 <"" but now I'm starting to think that isn't how it works.... I'd appreciate any clarification on how to build this type of multiple criteria into an array formula. Thanks! Keith -- The enclosed questions or comments are entirely mine and don't represent the thoughts, views, or policy of my employer. Any errors or omissions are my own. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
array formula(s)- not working, need some help please (problem with an 'AND')
Bob and Barb-
Thank you for pointing me to the sumproduct formula. I think it will give me what I need, but I saw that both of you include a double negative (--) in front of your secondary conditions. I didn't see anything about that in the helpfile- I'm assuming that prevents some other potential problem with the formula....can you enlight me as to what that prevents or achieves? Just to make sure I don't accidently mess it up ;-) Thanks!! Keith "Bob Phillips" wrote in message ... Something along the lines of =IF(AND(D3<D2,LEN(D3)0),SUMPRODUCT(--(D$3:D$3000=D3),--(J$3:J$3000=""),AA$ 3:AA$3000)),"") just add more conditions like --(range, test_against) This is not an array formula -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "KR" wrote in message ... Column AA= numeric count of program participation Column D= list of manager names (name repeats on each line) Column G= list of employees by manager (plus one blank cell for manager/self) Column J = exclusion criteria basically I'm trying to get a sum of how much participation has occurred in each manager's area, without including that manager's participation, and only for people without exclusion criteria- so I need to sum the values in AA once per manager, excluding rows where Column G is blank or Column J is not blank. I got as far as this first array formula, which works to sum up everything for that manager, but includes the manager's row, and sums regardless of exclusion criteria: 'using IF(AND(D3<D2,LEN(D3)0),<stuff,"") to only show the total once per manager name {=IF(AND(D3<D2,LEN(D3)0),SUM(IF(D$3:D$3000=D3,AA $3:AA$3000,0)),"") } However, I still need to only count rows with employee names, and exclude those that should be excluded, so I tried to add the exclusion criteria first, and can't get it working: {=IF(AND(D3<D2,LEN(D3)0),SUM(IF(AND(D$3:D$3000=D 3,J$3:J$3000=""),AA$3:AA$3 000,0)),"") } something about adding the AND formula makes my result zero, even when I know there should be some value returned. -- AND(D$3:D$3000=D3,J$3:J$3000="") I would think that this would check each row one at a time, for example, include AA5 in the sum only if D5=D3 /and/ J5 <"" but now I'm starting to think that isn't how it works.... I'd appreciate any clarification on how to build this type of multiple criteria into an array formula. Thanks! Keith -- The enclosed questions or comments are entirely mine and don't represent the thoughts, views, or policy of my employer. Any errors or omissions are my own. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
array formula(s)- not working, need some help please (problem with an 'AND')
simply put, the test
(D$3:D$3000=D3) returns an array of TRUE/FALSE values. Performing an arithmetic operation on it transforms this to an array of 1/0 values, which SP can use to multiply by the actual values to get only the values where a condition is met. So a single unary, -, transforms the TRUE/FALSE to 1/0, but a negative 1. So a second is required to get it back to positive 1. Thee is a lot more detail at http://www.xldynamic.com/source/xld.SUMPRODUCT.html . -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "KR" wrote in message ... Bob and Barb- Thank you for pointing me to the sumproduct formula. I think it will give me what I need, but I saw that both of you include a double negative (--) in front of your secondary conditions. I didn't see anything about that in the helpfile- I'm assuming that prevents some other potential problem with the formula....can you enlight me as to what that prevents or achieves? Just to make sure I don't accidently mess it up ;-) Thanks!! Keith "Bob Phillips" wrote in message ... Something along the lines of =IF(AND(D3<D2,LEN(D3)0),SUMPRODUCT(--(D$3:D$3000=D3),--(J$3:J$3000=""),AA$ 3:AA$3000)),"") just add more conditions like --(range, test_against) This is not an array formula -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "KR" wrote in message ... Column AA= numeric count of program participation Column D= list of manager names (name repeats on each line) Column G= list of employees by manager (plus one blank cell for manager/self) Column J = exclusion criteria basically I'm trying to get a sum of how much participation has occurred in each manager's area, without including that manager's participation, and only for people without exclusion criteria- so I need to sum the values in AA once per manager, excluding rows where Column G is blank or Column J is not blank. I got as far as this first array formula, which works to sum up everything for that manager, but includes the manager's row, and sums regardless of exclusion criteria: 'using IF(AND(D3<D2,LEN(D3)0),<stuff,"") to only show the total once per manager name =IF(AND(D3<D2,LEN(D3)0),SUM(IF(D$3:D$3000=D3,AA$ 3:AA$3000,0)),"") } However, I still need to only count rows with employee names, and exclude those that should be excluded, so I tried to add the exclusion criteria first, and can't get it working: {=IF(AND(D3<D2,LEN(D3)0),SUM(IF(AND(D$3:D$3000=D 3,J$3:J$3000=""),AA$3:AA$3 000,0)),"") } something about adding the AND formula makes my result zero, even when I know there should be some value returned. -- AND(D$3:D$3000=D3,J$3:J$3000="") I would think that this would check each row one at a time, for example, include AA5 in the sum only if D5=D3 /and/ J5 <"" but now I'm starting to think that isn't how it works.... I'd appreciate any clarification on how to build this type of multiple criteria into an array formula. Thanks! Keith -- The enclosed questions or comments are entirely mine and don't represent the thoughts, views, or policy of my employer. Any errors or omissions are my own. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
array formula(s)- not working, need some help please (problem
You can accomplish the same type coercion without the "--" by directly
multiplying the conditions together, as in SUMPRODUCT( (D$3:D$3000=D3)*(J3:J$3000="") ,AA$3:AA$3000) This approach extends naturally to more complicated combinations of conditions, since "+" corresponds to "OR" just as "*" corresponds to "AND". Moreover operator precidence is the same for Booleans as in regular arithmetic, so it should be clear where additional parentheses are needed in expressions of this type. Jerry "KR" wrote: Bob and Barb- Thank you for pointing me to the sumproduct formula. I think it will give me what I need, but I saw that both of you include a double negative (--) in front of your secondary conditions. I didn't see anything about that in the helpfile- I'm assuming that prevents some other potential problem with the formula....can you enlight me as to what that prevents or achieves? Just to make sure I don't accidently mess it up ;-) Thanks!! Keith "Bob Phillips" wrote in message ... Something along the lines of =IF(AND(D3<D2,LEN(D3)0),SUMPRODUCT(--(D$3:D$3000=D3),--(J$3:J$3000=""),AA$ 3:AA$3000)),"") just add more conditions like --(range, test_against) This is not an array formula -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "KR" wrote in message ... Column AA= numeric count of program participation Column D= list of manager names (name repeats on each line) Column G= list of employees by manager (plus one blank cell for manager/self) Column J = exclusion criteria basically I'm trying to get a sum of how much participation has occurred in each manager's area, without including that manager's participation, and only for people without exclusion criteria- so I need to sum the values in AA once per manager, excluding rows where Column G is blank or Column J is not blank. I got as far as this first array formula, which works to sum up everything for that manager, but includes the manager's row, and sums regardless of exclusion criteria: 'using IF(AND(D3<D2,LEN(D3)0),<stuff,"") to only show the total once per manager name {=IF(AND(D3<D2,LEN(D3)0),SUM(IF(D$3:D$3000=D3,AA $3:AA$3000,0)),"") } However, I still need to only count rows with employee names, and exclude those that should be excluded, so I tried to add the exclusion criteria first, and can't get it working: {=IF(AND(D3<D2,LEN(D3)0),SUM(IF(AND(D$3:D$3000=D 3,J$3:J$3000=""),AA$3:AA$3 000,0)),"") } something about adding the AND formula makes my result zero, even when I know there should be some value returned. -- AND(D$3:D$3000=D3,J$3:J$3000="") I would think that this would check each row one at a time, for example, include AA5 in the sum only if D5=D3 /and/ J5 <"" but now I'm starting to think that isn't how it works.... I'd appreciate any clarification on how to build this type of multiple criteria into an array formula. Thanks! Keith -- The enclosed questions or comments are entirely mine and don't represent the thoughts, views, or policy of my employer. Any errors or omissions are my own. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
array formula(s)- not working, need some help please (problem
When you say + corresponds to or you MAY want to add that under certain
circumstances you will need to compare the or result (to something along the lines of 0) Otherwise you may end up with an overstated amount. The reason I say this is that when I was first learning about +, all of the examples I saw used it as Or and I assumed it was completely interchangeable until I started seeing other posters adding a condition (to posts where I used it without adding a conditon.) It wasn't until then that I realized the potential for overstating the amount. -- Kevin Vaughn "Jerry W. Lewis" wrote: You can accomplish the same type coercion without the "--" by directly multiplying the conditions together, as in SUMPRODUCT( (D$3:D$3000=D3)*(J3:J$3000="") ,AA$3:AA$3000) This approach extends naturally to more complicated combinations of conditions, since "+" corresponds to "OR" just as "*" corresponds to "AND". Moreover operator precidence is the same for Booleans as in regular arithmetic, so it should be clear where additional parentheses are needed in expressions of this type. Jerry "KR" wrote: Bob and Barb- Thank you for pointing me to the sumproduct formula. I think it will give me what I need, but I saw that both of you include a double negative (--) in front of your secondary conditions. I didn't see anything about that in the helpfile- I'm assuming that prevents some other potential problem with the formula....can you enlight me as to what that prevents or achieves? Just to make sure I don't accidently mess it up ;-) Thanks!! Keith "Bob Phillips" wrote in message ... Something along the lines of =IF(AND(D3<D2,LEN(D3)0),SUMPRODUCT(--(D$3:D$3000=D3),--(J$3:J$3000=""),AA$ 3:AA$3000)),"") just add more conditions like --(range, test_against) This is not an array formula -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "KR" wrote in message ... Column AA= numeric count of program participation Column D= list of manager names (name repeats on each line) Column G= list of employees by manager (plus one blank cell for manager/self) Column J = exclusion criteria basically I'm trying to get a sum of how much participation has occurred in each manager's area, without including that manager's participation, and only for people without exclusion criteria- so I need to sum the values in AA once per manager, excluding rows where Column G is blank or Column J is not blank. I got as far as this first array formula, which works to sum up everything for that manager, but includes the manager's row, and sums regardless of exclusion criteria: 'using IF(AND(D3<D2,LEN(D3)0),<stuff,"") to only show the total once per manager name {=IF(AND(D3<D2,LEN(D3)0),SUM(IF(D$3:D$3000=D3,AA $3:AA$3000,0)),"") } However, I still need to only count rows with employee names, and exclude those that should be excluded, so I tried to add the exclusion criteria first, and can't get it working: {=IF(AND(D3<D2,LEN(D3)0),SUM(IF(AND(D$3:D$3000=D 3,J$3:J$3000=""),AA$3:AA$3 000,0)),"") } something about adding the AND formula makes my result zero, even when I know there should be some value returned. -- AND(D$3:D$3000=D3,J$3:J$3000="") I would think that this would check each row one at a time, for example, include AA5 in the sum only if D5=D3 /and/ J5 <"" but now I'm starting to think that isn't how it works.... I'd appreciate any clarification on how to build this type of multiple criteria into an array formula. Thanks! Keith -- The enclosed questions or comments are entirely mine and don't represent the thoughts, views, or policy of my employer. Any errors or omissions are my own. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Formulas take waaaay too long... | Excel Worksheet Functions | |||
array formulas | Excel Worksheet Functions | |||
Simple formulas in existing Excel 2002 no longer working. | Excel Worksheet Functions | |||
Array not working correctly Returns FALSE on second part | Excel Discussion (Misc queries) | |||
Formulas not working | Excel Worksheet Functions |