If formular

T

Therese

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

bpeltzer

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

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

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
 
S

Sloth

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