Re: conditional Formula

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: eggman (eatlotsof_at_spam.com)
Date: 10/08/04 

Date: Fri, 8 Oct 2004 11:35:10 -0500

to clarify my previous post, you can get rid of the "--" for each array that
contains numbers. Text values need to be coerced. So if column X contains
numbers, you do not need to have the "--" infront of that range:

e.g..

=SUMPRODUCT(--($V$3:$V$72=F$5),--($W$3:$W$72=$C24),($X$3:$X$72))

"eggman" <eatlotsof@spam.com> wrote in message
news:eQR6LPVrEHA.1232@TK2MSFTNGP11.phx.gbl...
> one way:
>
> =SUMPRODUCT(--($V$3:$V$72=F$5),--($W$3:$W$72=$C24),--($X$3:$X$72))
>
> If any of the ranges contain numbers and not text, you should be able to
get
> rid of the "--" on the front of each array.
>
> HTH
>
> "Joe" <someone@microsoft.com> wrote in message
> news:uDfikMVrEHA.2008@TK2MSFTNGP12.phx.gbl...
> > =SUM(IF($V$3:$V$72=F$5,IF($W$3:$W$72=$C24,$X$3:$X$72,0),0))
> >
> > I am trying to get a list of data into table format.
> >
> > if the data in any of the 'V' column agrees to the label at the top and
if
> > the data in any of the 'W' column agrees to the label at the side, then
> add
> > up the relevant cells to put it in the table.
> >
> > I would like to do this with a formula as the data is likely to change
on
> a
> > monthly basis and I will only need to update the list.
> >
> > I have put together the above formula but it comes back to '0'.
> >
> > Can anybody shed any light, it would be very much appreciated.
> >
> > Thanks
> >
> >
>
>

Relevant Pages

  • Re: rotating random popups
    ... Can you clarify what you mean by 'put references to the good ones in the new ... testing I've done it modifies the array by eliminating the current random ... winSrc: I tested it with a document.write: ... for (J in winArr) if A.push ...
    (comp.lang.javascript)
  • Re: simple Garbage Collection question.
    ... unless I set the array to null releasing the remaining reference to ... I'd assume that task queue ... To clarify what I meant by nullify, I meant setting my array to null ...
    (comp.lang.java.programmer)
  • Re: beginner help with sequential and binary search
    ... in a 7 element array. ... I was trying to clarify and you have clipped the part ... other readers. ... (which can be done with either an equality comparison or checking if the ...
    (comp.programming)
  • Re: linux doesnt like me
    ... perldoc -q "How do I tell the difference between errors from the shell and perl" ... #initialize array and store data in the array ... Why do you think that there is a carriage return to get rid of and why do you ... think that chomp() will get rid of it? ...
    (perl.beginners)
  • Trouble with code that is broken over multiple lines (space_)
    ... .CommandText = Array(_ ... Sum(fct_CommissionAccrued.AccruedAmount) AS 'Sum of ... "rID AND fct_CommissionAccrued.CommissionTypeID = ...
    (microsoft.public.excel.programming)