# How to Control a formula by a character in adjacent column on same

J

#### Joe

I have the following example

A
1 aaa 1
2 aaa 2
3 bbb 3 X
4 ccc 4
5 ccc 5
6 3
7 3
8 9

Cell A1 contains "aaa"
Cell B1 contains "1"
Cell D3 contains "X"

Formula in B6 is =SUMIF(A1:A5,"A",B1:B5) and returns 3
Formula in B7 is =SUMIF(A1:A5,"B",B1:B5) and returns 3
Formula in B8 is =SUMIF(A1:A5,"C",B1:B5) and returns 9

I want to be able to add a function that will allow me to control the
formula by whether there is an X on the same row a cople of columns over.

So if I put an X on the same line a couple of columns over the formual will
do nothing.

Is there a way to do this?

Thank you.

P

#### Pete_UK

Perhaps you've got "*A*" in your first formula. You could put this in
B6:

=SUMPRODUCT(--(isnumber(SEARCH("a",A\$1:A\$5)),--(D\$1 \$5<>"X"),B\$1:B\$5)

Copy this into B7:B8 and change the "a" to "b" and "c" respectively.

Hope this helps.

Pete

K

#### Kim Glensor

Using SUMIFS instead of just SUMIF you can specify multiple criteria, so set
your first criteria as you have, then set the second to check for the
presence of that X or not.

J

#### Joe

Pete-

I can't past the formula you provided for some reason. It says the formula
contains an error.

-Joe