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

On Jan 12, 12:58*am, Joe wrote:
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-

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

-Joe

"Pete_UK" wrote:

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

On Jan 12, 12:58 am, Joe wrote:
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.

.

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.

That worked great. Thanks Kim!

"Kim Glensor" wrote:

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.