Re: array formula(s)- not working, need some help please (problem with an 'AND')

Tech-Archive recommends: Fix windows errors by optimizing your registry

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" <nospam@xxxxxxxxxx> wrote in message
news:OtH8CCEoGHA.2068@xxxxxxxxxxxxxxxxxxxxxxx
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" <bob.NGs@xxxxxxxxxxxxx> wrote in message
news:%23ypsf6DoGHA.2100@xxxxxxxxxxxxxxxxxxxxxxx
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" <nospam@xxxxxxxxxx> wrote in message
news:el89JnDoGHA.4728@xxxxxxxxxxxxxxxxxxxxxxx
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=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: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.








.

Relevant Pages

  • Re: How to calculate: sumproduct / db??
    ... returns an array of TRUE/FALSE values. ... single unary, -, transforms the TRUE/FALSE to 1/0, but a negative 1. ... number of pieces fruit in a box ...
    (microsoft.public.excel.programming)
  • Re: repeating columnar data
    ... The formula is an array formula. ... to include cells with zero and exclude blank cells: ... include the cells with 0 or a number and exclude the blank cells. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Alternative to Application.Caller in array functions to avoid wrong results?
    ... Seems to be fixed in Excel 2007. ... These array functions use Application.Caller to determine the size and shape ... When it is invoked by an array formula in A1:B5, e.g. =myInts, it returns ... I have instrumented the testit() macro and myIntsfunction to make the ...
    (microsoft.public.excel.programming)
  • Re: Database functions should use criteria in formula, as 1-2-3 do
    ... >>quickly and create a crosstab. ... > I won't say this is as elegant as it is in 123, but an equivalent Excel ... the array formula reduces to ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Alternative to Application.Caller in array functions to avoid wrong results?
    ... Array Functions ... So for demonstration purposes, consider the array function myInts() below, ... I have instrumented the testit() macro and myIntsfunction to make the ... the array formula cannot return a set of unique integers. ...
    (microsoft.public.excel.programming)