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

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

 

[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

 

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

 

[Joe]

Pete-

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

-Joe

 

[Joe]

That worked great. Thanks Kim!