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

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 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

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

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

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