On Mon, Jan 30, 2012 at 03:35, Brian Barker <[email protected]> wrote: > 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
Oh! That is great! I have a lot of uses for this. Thanks Brian! Wade -- Registered Linux User: #480675 Registered Linux Machine: #408606 Linux since June 2005 -- ----------------------------------------------------------------- To unsubscribe send email to [email protected] For additional commands send email to [email protected] with Subject: help
