If formular
Hi
I want to sum the numbers i have written in row a. In row b, I have a row where I put an x when I want the number beside(the number in row a) to dissappear. I have tried cond. formatting, but the sum at the bottom counts the numbers anyway off course. How do I do that? Does anyone have an IF formular with a circular reference or do I really have to make a new row wih the numbers? Thanks in advance :0) -- Therese |
If formular
The sumif function will conditionally add numbers in one range based on a
criteria in a different range. So =sumif(b:b,"x",a:a) will add all the values in a where you have entered an x in b. If you're using x to exclude (vs include), try =sum(a:a)-sumif(b:b,"x",a:a) "Therese" wrote: Hi I want to sum the numbers i have written in row a. In row b, I have a row where I put an x when I want the number beside(the number in row a) to dissappear. I have tried cond. formatting, but the sum at the bottom counts the numbers anyway off course. How do I do that? Does anyone have an IF formular with a circular reference or do I really have to make a new row wih the numbers? Thanks in advance :0) -- Therese |
If formular
You could use the conditional formatting then a formula like =SUMIF(B:B,"<x",A:A) only sums values in column A where there is not an "x" in column b.... -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=528332 |
If formular
If you just want to sum a where b is not and x
=SUMPRODUCT(--(B2:B500<"x"),A2:A500) you can use conditional formatting to "hide" the numbers select A2:A500 with A2 as active, do formatconditional formatting, formula is then use =$B2="x" click the format button and select white font, click OK twice all other options would require VBA to clear numbers from A -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Therese" wrote in message ... Hi I want to sum the numbers i have written in row a. In row b, I have a row where I put an x when I want the number beside(the number in row a) to dissappear. I have tried cond. formatting, but the sum at the bottom counts the numbers anyway off course. How do I do that? Does anyone have an IF formular with a circular reference or do I really have to make a new row wih the numbers? Thanks in advance :0) -- Therese |
If formular
Hey you guys you are terrific. Thanks
-- Therese "daddylonglegs" skrev: You could use the conditional formatting then a formula like =SUMIF(B:B,"<x",A:A) only sums values in column A where there is not an "x" in column b.... -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=528332 |
If formular
you could also use the autofilter to filter out every row with an x. You
would need to replace SUM with SUBTOTAL. The SUBTOTAL function ingnores all hidden rows. "Therese" wrote: Hi I want to sum the numbers i have written in row a. In row b, I have a row where I put an x when I want the number beside(the number in row a) to dissappear. I have tried cond. formatting, but the sum at the bottom counts the numbers anyway off course. How do I do that? Does anyone have an IF formular with a circular reference or do I really have to make a new row wih the numbers? Thanks in advance :0) -- Therese |
All times are GMT +1. The time now is 12:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com