sumif formulas

[ibhavn]

I have a listing of salary figures for my organization. Several positions are
currently vacant, but the salary needed to fill the position is still listed.


How do I sum the range of figures to EXCLUDE the figures on rows where the A
column has VACANT typed in it?

Thank you for your help.

 

[T. Valko]

Maybe this:

=SUMIF(A1:A100,"<>Vacant",B1:B100)

 

[ibhavn]

Wonderful! That worked, now if I just understood why it worked. Thank you
for your prompt response!

 

[Spiky]

Wonderful! That worked, now if I just understood why it worked. Thank you
for your prompt response!

Because nobody bothered to put the $B!b(B sign into ANY generation of
ASCII, and Microsoft thought it would be cool to ignore it, too, we
use <> to mean "not equal". Or you could say it, "either less than or
greater than".

So for the formula: you are summing the B column cells if the
corresponding A column cell is NOT "Vacant".

 

[T. Valko]

You're welcome!

=SUMIF(A1:A100,"<>Vacant",B1:B100)

<> means "is not equal to"

So in plain English, sum the cells in column B where the cells in column A
is not equal to vacant.

 

[Dave Peterson]

In case you decide to move the text vacant to a cell (say C1) and want to change
the formula to point to that cell.

You could use:
=SUMIF(A1:A100,"<>"&c1,B1:B100)

In fact, you could even do this:
=SUMIF(A1:A100,"<>"&"Vacant",B1:B100)