addding/omitting "x"

[tleehh]

using this formula, =sumif(a10:a20,{"s1"},b10:b20)
I am trying to add all the values in b10:b20 excluding that is mark "x" in
colume C.

 

[Dave Peterson]

First, you don't need the {}'s in your existing formula.

Second,
=sumproduct(--(a10:a20="s1"),--(c10:c20<>"x"),b10:b20)

Adjust the ranges to match--but you can't use whole columns (except in xl2007+).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

============

If you're using xl2007, there's a new =countifs() function that will do the same
kind of thing.

 

[Otávio Alves Ribeiro]

Hi there.
1. Add all values in B10:B10 excluding those which have an X in the same row
column A:

=SUMIF(A10:A20,"<>x",B10:B20)

2. Add all values in B10:B10 excluding those which have an X in the same row
column C:

=SUMIF(C10:C20,"<>x",B10:B20)

b.t.w.: in my tests, I needed to format the criteria range (A10:A20 or
C10:C20) as text, select all the range and press DELETE before the formula
start working

Regards,
Otávio