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.