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 ------------------------------------------------------------------------ kayard's Profile: http://www.excelforum.com/member.php...o&userid=24716 View this thread: http://www.excelforum.com/showthread...hreadid=569507 |
Difficult (for me) question about conditional SUM
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="T RUE")*($C$1:$C$1000)) Hopefully one of those will work for you. I hope this helps, Conan "kayard" wrote in message ... 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 |
Difficult (for me) question about conditional SUM
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" wrote in message ... 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 |
Difficult (for me) question about conditional SUM
thanks, but I actually have to make it even harder. Let's make this example. I have column A containing either true o fals -- kayar ----------------------------------------------------------------------- kayard's Profile: http://www.excelforum.com/member.php...fo&userid=2471 View this thread: http://www.excelforum.com/showthread.php?threadid=56950 |
Difficult (for me) question about conditional SUM
Not sure I understand the problem.
See the values of : =True*True*100 =False*True*100 =False*False*100 NickHK "kayard" wrote in message ... 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 |
Difficult (for me) question about conditional SUM
I'll try to explain better. I have in A1:A100 either numbers or #N/A. In B1:B100 there is eithe TRUE or FALSE. I want to calculate the average of A1:A100 but only o those rows where there is an actual number (Not #N/A) and wher condition in column B is true. Thank -- kayar ----------------------------------------------------------------------- kayard's Profile: http://www.excelforum.com/member.php...fo&userid=2471 View this thread: http://www.excelforum.com/showthread.php?threadid=56950 |
Difficult (for me) question about conditional SUM
I'll try to explain better. I have in A1:A100 either numbers or #N/A. In B1:B100 there is eithe TRUE or FALSE. I want to calculate the average of A1:A100 but only o those rows where there is an actual number (Not #N/A) and wher condition in column B is true. Thank -- kayar ----------------------------------------------------------------------- kayard's Profile: http://www.excelforum.com/member.php...fo&userid=2471 View this thread: http://www.excelforum.com/showthread.php?threadid=56950 |
All times are GMT +1. The time now is 12:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com