At 22:31 29/01/2012 -0600, Wade Smart wrote:
On Thu, Jan 26, 2012 at 20:07, Brian Barker wrote:
At 19:21 26/01/2012 -0600, Wade Smart wrote:
I need a total based on two columns.
For example:
A1 = Black
A3 = Dk Blue
A9 = White
C1 = x
C3 =
C9 = x
If a1=Black AND c1 = x then count this as one.
Can you count if with two columns?
Yes: =SUMPRODUCT(A1:A99="Black";C1:C99="x")
The conditions are either TRUE or FALSE. Â When
interpreted as numbers, these values are 1 and
0 respectively. Â The product gives 1 only if
both components are 1, so it effectively ANDs
the two conditions. Â The resulting true (=1) values are then summed.
I need to add one more step to this. I have
another column.. D lets say. It has a number in
it. In my original example I needed to count a
entry if it was active. So I have a lot of
entries with Black as one indicator and some of
those Black entries will have an "x" in another
column. I needed to sum those up. And sumproduct() did great.
Now I would like to see if there is a automated way of doing this next step:
If A1=Black and C1="x", I need to sum column. AB.
(Er, is that D or AB?)
Easy: =SUMPRODUCT(A1:A99="Black";C1:C99="x";AB1:AB99)
SUMPRODUCT() can take more than two
arguments. As before, the product of the first
two logical tests is either TRUE (1) or FALSE
(0). Now we multiply each value in column AB by
this switch and then sum the results.
I trust this also helps.
Brian Barker
--
-----------------------------------------------------------------
To unsubscribe send email to [email protected]
For additional commands send email to [email protected]
with Subject: help
