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.