K
KR
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$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$3000=D3,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$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
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$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$3000=D3,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$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