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

Advertisements

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: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
 
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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top