Help with countif using two columns

[Vipulparbat]

Hi, the answer to my problem is pobably obvious but i need help.

I have a database in which column B returns True or False values and
column E returns true or false values .

I would like to count the number of cells in which False appears in
both columns in the same row, ie if b4 and e4 are both false, then it
returns the value 1.

The range is from rows 3 to 383.

I have tried these formulas but they keep on returning 0.

=SUMPRODUCT((b3:b383="FALSE"),(E3:E383="FALSE"))

=SUMPRODUCT((CZ3:CZ383="FALSE")*(E3:E383="FALSE")).

your help would be gratefully appreciated.

 

[Dave Peterson]

If those values that are returned are really the Boolean TRUE and FALSE, then
remove the double quotes:
=SUMPRODUCT(--(b3:b383=FALSE),--(E3:E383=FALSE))
or
=SUMPRODUCT((CZ3:CZ383=FALSE)*(E3:E383=FALSE))

But excel will treat empty cells as FALSE.

You may want something like:
=SUMPRODUCT(--(b3:b383<>""),--(b3:b383=FALSE),
--(e3:e383<>""),--(e3:e383=FALSE))

Adjust the ranges to match--but you can't use whole columns.

=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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

 

[Dave Peterson]

If those values that are returned are really the Boolean TRUE and FALSE, then
remove the double quotes:
=SUMPRODUCT(--(b3:b383=FALSE),--(E3:E383=FALSE))
or
=SUMPRODUCT((CZ3:CZ383=FALSE)*(E3:E383=FALSE))

But excel will treat empty cells as FALSE.

You may want something like:
=SUMPRODUCT(--(b3:b383<>""),--(b3:b383=FALSE),
--(e3:e383<>""),--(e3:e383=FALSE))

Adjust the ranges to match--but you can't use whole columns.

=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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

 

[Franz Verga]

Hi, the answer to my problem is pobably obvious but i need help.

I have a database in which column B returns True or False values and
column E returns true or false values .

I would like to count the number of cells in which False appears in
both columns in the same row, ie if b4 and e4 are both false, then it
returns the value 1.

The range is from rows 3 to 383.

I have tried these formulas but they keep on returning 0.

=SUMPRODUCT((b3:b383="FALSE"),(E3:E383="FALSE"))

=SUMPRODUCT((CZ3:CZ383="FALSE")*(E3:E383="FALSE")).

your help would be gratefully appreciated.

Try with this:

=SUMPRODUCT((BZ3:BZ383=FALSE)*(E3:E383=FALSE)).


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Franz Verga]

Hi, the answer to my problem is pobably obvious but i need help.

I have a database in which column B returns True or False values and
column E returns true or false values .

I would like to count the number of cells in which False appears in
both columns in the same row, ie if b4 and e4 are both false, then it
returns the value 1.

The range is from rows 3 to 383.

I have tried these formulas but they keep on returning 0.

=SUMPRODUCT((b3:b383="FALSE"),(E3:E383="FALSE"))

=SUMPRODUCT((CZ3:CZ383="FALSE")*(E3:E383="FALSE")).

your help would be gratefully appreciated.

Try with this:

=SUMPRODUCT((BZ3:BZ383=FALSE)*(E3:E383=FALSE)).


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Vipulparbat]

Thanks Frank, I'm sure I tried that but it didnt work before. It works
fine now

 

[Vipulparbat]

Thanks Frank, I'm sure I tried that but it didnt work before. It works
fine now

 

[Franz Verga]

Thanks Frank, I'm sure I tried that but it didnt work before. It
works fine now

You're welcome. But... Who is Frank?


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Franz Verga]

Thanks Frank, I'm sure I tried that but it didnt work before. It
works fine now

You're welcome. But... Who is Frank?


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy