ExcelBanter - View Single Post

T. Valko

"ab3d4u" wrote in message
...

T. Valko;2499598 Wrote:
In versions prior to Excel 2007 Sumproduct can have up to 30 arguments.
In
Excel 2007 the number of arguments was increased to 64 (I think it was
64).

See this for a comprehensive explanation of Sumproduct:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP

"tsterople" wrote in message
...-
Are there an infinite number of possible arrays, infinite iterative?
--
tsterople

"T. Valko" wrote:
-
=SUMPRODUCT(--(A1:A20=1),--(B1:B20=1),D1:D20)

These expressions will return an array of either TRUE or FALSE:

(A1:A20=1)
(B1:B20=1)

The "--" coerces the TRUE and FALSE to either 1 or 0. Then, all 3
arrays
are
multiplied together then summed for the result. Based on the first few

cells
in your range it would look like this:

1*1*8.911 = 8.911
1*1*9.058 = 9.058
1*1*9.106 = 9.106
1*1*9.031 = 9.031
1*0*9.883 = 0

=SUMPRODUCT({8.911;9.058;9.106;9.031;0}) = 36.106

--
Biff
Microsoft Excel MVP

"tsterople" wrote in message
...-
OUT F#*%KING STANDING BIFF! I try very hard to answer my own
questions
and
have labored over this one for weeks, thank you so much...can you
elaborate
on the notational format,(--)?
--
tsterople

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(A1:A20=1),--(B1:B20=1),D1:D20)

--
Biff
Microsoft Excel MVP

"tsterople" wrote in message
...
I need a formula to sum a conditional array, If A is 1 and B is 1
SUM
the
cooresponding D values 8.911, 9.058, 9.106, 9.031. In this example
I
have
A
part 1, B Appraiser 1, C number of trials i.e. 1, 2, 3, 4 and D
values
8.911,
9.058, 9.106, 9.031.

A B C D
1 1 1 8.911
1 1 2 9.058
1 1 3 9.106
1 1 4 9.031
1 2 1 9.883
1 2 2 9.292
1 2 3 9.793
1 2 4 9.353
1 3 1 9.491
1 3 2 9.731
1 3 3 9.057
1 3 4 9.304
2 1 1 13.632
2 1 2 13.827
2 1 3 13.184
2 1 4 13.532
2 2 1 15.32
2 2 2 15.037
2 2 3 14.884
2 2 4 15.31
2 3 1 16.317
2 3 2 16.48
2 3 3 16.31
2 3 4 16.256

--
tsterople

-

--

I do not know if I am posting in the right spot. I have an icon between
the last part of the formula D1 and D2. How do I get rid of it? What
sign/function does this icon represent?
Muchas gracias

--
ab3d4u

Sorry, I have no idea what you're talking about.

A wild guess is that html might interpret this string ":D" as a "smilie".

Other than that, I got nothin!

--
Biff
Microsoft Excel MVP