Conditional formula

  • Thread starter Thread starter tsterople
  • Start date Start date
T

tsterople

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
 
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,(--)?
 
=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
 
T. Valko;2499598 said:
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" (e-mail address removed) wrote in message
Are there an infinite number of possible arrays, infinite iterative?
--
tsterople


:
-
=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" (e-mail address removed) 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


:

Try this:

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

--
Biff
Microsoft Excel MVP


"tsterople" (e-mail address removed) 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


-


--
Click to expand...

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 said:
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
Click to expand...

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!
 
Back
Top