Is there a formula that will check 2 columns in the same row. I would like to
have 1 cell that will keep a running total if columns D or Column E has the
words "Error". There are some cases where both columns will have this word
but I would only like the number to be counted once.

I think that there is a very simple formula for it, but I forget it at the
moment. Could someone please help

Thanks

=sumproduct(--(((a1:a10="error")+(b1:b10="error"))0))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html




FormulaQuestioner wrote:

Is there a formula that will check 2 columns in the same row. I would like to
have 1 cell that will keep a running total if columns D or Column E has the
words "Error". There are some cases where both columns will have this word
but I would only like the number to be counted once.

I think that there is a very simple formula for it, but I forget it at the
moment. Could someone please help

Thanks

--

Dave Peterson

That code worked great.

I am also looking at having one that will work for the following

Cell B37 has a formula that will checked C1:C30 and will have the following
condition:

Count the number of times that the word "Missing" appears in C1:C30 IF
column D1:D30 or D1:D30 does not say "Do Not Use"

I tired to figure out the format from the link in the reply but was unable
to. Any help would be appreciated.

"Dave Peterson" wrote:

=sumproduct(--(((a1:a10="error")+(b1:b10="error"))0))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html




FormulaQuestioner wrote:

Is there a formula that will check 2 columns in the same row. I would like to
have 1 cell that will keep a running total if columns D or Column E has the
words "Error". There are some cases where both columns will have this word
but I would only like the number to be counted once.

I think that there is a very simple formula for it, but I forget it at the
moment. Could someone please help

Thanks

--

Dave Peterson

Maybe:

To check if column C = "Missing", but column D is different from "do not use"
=sumproduct(--(c1:c30="Missing"),--(d1:d30<"Do not use"))
(you wrote d1:d30 twice???)

or
To check if column C = "Missing",
and (column D is different from "do not use"
AND column E is also different from "do not use")

=SUMPRODUCT(--(C1:C30="Missing"),
--(((D1:D30="Do not use")+(E1:E30="Do not use")=0)))





FormulaQuestioner wrote:

That code worked great.

I am also looking at having one that will work for the following

Cell B37 has a formula that will checked C1:C30 and will have the following
condition:

Count the number of times that the word "Missing" appears in C1:C30 IF
column D1:D30 or D1:D30 does not say "Do Not Use"

I tired to figure out the format from the link in the reply but was unable
to. Any help would be appreciated.

"Dave Peterson" wrote:

=sumproduct(--(((a1:a10="error")+(b1:b10="error"))0))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html




FormulaQuestioner wrote:

Is there a formula that will check 2 columns in the same row. I would like to
have 1 cell that will keep a running total if columns D or Column E has the
words "Error". There are some cases where both columns will have this word
but I would only like the number to be counted once.

I think that there is a very simple formula for it, but I forget it at the
moment. Could someone please help

Thanks

--

Dave Peterson


--

Dave Peterson

I meant it to be

Count the number of times that the word "Missing" appears in C1:C30 IF
column D1:D30 or E1:E30 does not say "Do Not Use"

The code you gave me will seem to work.

Thank-you

"Dave Peterson" wrote:

Maybe:

To check if column C = "Missing", but column D is different from "do not use"
=sumproduct(--(c1:c30="Missing"),--(d1:d30<"Do not use"))
(you wrote d1:d30 twice???)

or
To check if column C = "Missing",
and (column D is different from "do not use"
AND column E is also different from "do not use")

=SUMPRODUCT(--(C1:C30="Missing"),
--(((D1:D30="Do not use")+(E1:E30="Do not use")=0)))





FormulaQuestioner wrote:

That code worked great.

I am also looking at having one that will work for the following

Cell B37 has a formula that will checked C1:C30 and will have the following
condition:

Count the number of times that the word "Missing" appears in C1:C30 IF
column D1:D30 or D1:D30 does not say "Do Not Use"

I tired to figure out the format from the link in the reply but was unable
to. Any help would be appreciated.

"Dave Peterson" wrote:

=sumproduct(--(((a1:a10="error")+(b1:b10="error"))0))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html




FormulaQuestioner wrote:

Is there a formula that will check 2 columns in the same row. I would like to
have 1 cell that will keep a running total if columns D or Column E has the
words "Error". There are some cases where both columns will have this word
but I would only like the number to be counted once.

I think that there is a very simple formula for it, but I forget it at the
moment. Could someone please help

Thanks

--

Dave Peterson


--

Dave Peterson