If formular

[Guest]

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)

 

[Guest]

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)

 

[daddylonglegs]

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

 

[Peo Sjoblom]

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

 

[Guest]

Hey you guys you are terrific. Thanks

 

[Guest]

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.