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

  • Thread starter Thread starter Joe
  • Start date Start date
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.
 
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:D$5<>"X"),B$1:B$5)

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

Hope this helps.

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

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

-Joe
 
Back
Top