Re: conditional Formula

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Aladin Akyurek (akyurek_at_xs4all.nl)
Date: 10/08/04 

Date: Fri, 8 Oct 2004 19:14:25 +0200

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

contains 2 conditional expressions, or conditionals for short:

$V$3:$V$72=F$5

$W$3:$W$72=$C24

These would evaluate into arrays of truth values (not text values),
consisting of TRUE's and FALSE's, while the current design of SumProduct
requires them to evaluate into numerical arrays. Hence the double negation
to coerce the truth values into their Excel numerical equivalents: 1's and
0's, repectively. On the other hand,

$X$3:$X$72

is supposed to be numeric (the range to sum), although the comma syntax
allows SumProduct to behave like Sum, that is, ignore any text values in
that range. Prefixing the range to sum also with -- would coerce any
text-formatted numbers into true numbers with the risk of getting #VALUE!
errors regarding true text-values that occur in that range.

"eggman" <eatlotsof@spam.com> wrote in message
news:OogaITVrEHA.3324@TK2MSFTNGP15.phx.gbl...
> 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
>> >
>> >
>>
>>
>
>

Quantcast