Difficult (for me) question about conditional SUM

Hi, I'm back again asking for some help.

I have in column A and B 2 formulas that evaluate to either true or false. In column C I have a number. Say that I have 1000 rows; i want to sum only those values in column C where valuels in column A and B in the same row are both true.

Of course I can create a new column D where I AND booleans in A and B but This would make me create a new column ... I do not want this because in my file I have many many pairs of A & B. So I would have to create at least 25 more columns containing AND formulas...

I need something that doesn't oblige me to create any new column

Thanks in advance for any help

Sorry for my english, I hope I have made myself understood.

Paolo

Italy

kayard,

You might try this:

=sumproduct(($A$1:$A$1000=TRUE)*($B$1:$B$1000=TRUE)*($C$1:$C$1000))

OR

=sumproduct(($A$1:$A$1000="TRUE")*($B$1:$B$1000="TRUE")*($C$1:$C$1000))

Hopefully one of those will work for you.

I hope this helps,

Conan



"kayard" <[email protected]> wrote in message
news:[email protected]...
>
> Hi, I'm back again asking for some help.
>
> I have in column A and B 2 formulas that evaluate to either true or
> false. In column C I have a number. Say that I have 1000 rows; i want
> to sum only those values in column C where valuels in column A and B
> in the same row are both true.
>
> Of course I can create a new column D where I AND booleans in A and B
> but This would make me create a new column ... I do not want this
> because in my file I have many many pairs of A & B. So I would have to
> create at least 25 more columns containing AND formulas...
>
> I need something that doesn't oblige me to create any new column
>
> Thanks in advance for any help
>
> Sorry for my english, I hope I have made myself understood.
>
> Paolo
>
> Italy
>
>
> --
> kayard
> ------------------------------------------------------------------------
> kayard's Profile: http://www.excelforum.com/member.php...o&userid=24716
> View this thread: http://www.excelforum.com/showthread...hreadid=569507
>

You don't need to explicitly test for True. Use the fact that True=1 and
False=0 (on Excel worksheets anyway);
=SUMPRODUCT(A1:A9*B1:B9*C1:C9)

NickHK

"kayard" <[email protected]> wrote in
message news:[email protected]...
>
> Hi, I'm back again asking for some help.
>
> I have in column A and B 2 formulas that evaluate to either true or
> false. In column C I have a number. Say that I have 1000 rows; i want
> to sum only those values in column C where valuels in column A and B
> in the same row are both true.
>
> Of course I can create a new column D where I AND booleans in A and B
> but This would make me create a new column ... I do not want this
> because in my file I have many many pairs of A & B. So I would have to
> create at least 25 more columns containing AND formulas...
>
> I need something that doesn't oblige me to create any new column
>
> Thanks in advance for any help
>
> Sorry for my english, I hope I have made myself understood.
>
> Paolo
>
> Italy
>
>
> --
> kayard
> ------------------------------------------------------------------------
> kayard's Profile:
http://www.excelforum.com/member.php...o&userid=24716
> View this thread: http://www.excelforum.com/showthread...hreadid=569507
>

Not sure I understand the problem.
See the values of :
=True*True*100
=False*True*100
=False*False*100

NickHK

"kayard" <[email protected]> wrote in
message news:[email protected]...
>
> thanks, but I actually have to make it even harder.
>
> Let's make this example. I have column A containing either true or
> false
>
>
> --
> kayard
> ------------------------------------------------------------------------
> kayard's Profile:
http://www.excelforum.com/member.php...o&userid=24716
> View this thread: http://www.excelforum.com/showthread...hreadid=569507
>

I'll try to explain better.

I have in A1:A100 either numbers or #N/A. In B1:B100 there is either TRUE or FALSE. I want to calculate the average of A1:A100 but only of those rows where there is an actual number (Not #N/A) and where condition in column B is true.

Thanks