conditional formula using sumproduct
 

A--------------B-
6N FALSE
6N FALSE
7N TRUE
8N TRUE
6N TRUE

I am using this as an array formula in C2 but getting zero for an answer
when the correct answer would be 1. Can anyone tell me what I am doing
wrong. I am looking for the formula to count the number of times "TRUE" is
found in B2:B6 IF "6N" is also found in A2:A6. Any help would be greatly
appreciated.

SUMPRODUCT((A2:A6="6N")*(B2:B6="TRUE"))

correct answer =1

conditional formula using sumproduct
 

Hi,

Remove the quotes from TRUE and no need to array enter

=SUMPRODUCT((A2:A6="6N")*(B2:B6=TRUE))

Mike

"ferde" wrote:

A--------------B-
6N FALSE
6N FALSE
7N TRUE
8N TRUE
6N TRUE

I am using this as an array formula in C2 but getting zero for an answer
when the correct answer would be 1. Can anyone tell me what I am doing
wrong. I am looking for the formula to count the number of times "TRUE" is
found in B2:B6 IF "6N" is also found in A2:A6. Any help would be greatly
appreciated.

SUMPRODUCT((A2:A6="6N")*(B2:B6="TRUE"))

correct answer =1

conditional formula using sumproduct
 

I guess that your TRUE and FALSE are not text strings but Boolean values.
Try deleting the quote marks around TRUE.

In fact as a Boolean you can delete the =TRUE, so the formula simplifies to
=SUMPRODUCT((A2:A6="6N")*(B2:B6))
--
David Biddulph

"ferde" wrote in message
...
A--------------B-
6N FALSE
6N FALSE
7N TRUE
8N TRUE
6N TRUE

I am using this as an array formula in C2 but getting zero for an answer
when the correct answer would be 1. Can anyone tell me what I am doing
wrong. I am looking for the formula to count the number of times "TRUE"
is
found in B2:B6 IF "6N" is also found in A2:A6. Any help would be
greatly
appreciated.

SUMPRODUCT((A2:A6="6N")*(B2:B6="TRUE"))

correct answer =1

conditional formula using sumproduct
 

I knew I was getting close to solving this ....Thank you so much Mike.
...works great!

"Mike H" wrote:

Hi,

Remove the quotes from TRUE and no need to array enter

=SUMPRODUCT((A2:A6="6N")*(B2:B6=TRUE))

Mike

"ferde" wrote:

A--------------B-
6N FALSE
6N FALSE
7N TRUE
8N TRUE
6N TRUE

I am using this as an array formula in C2 but getting zero for an answer
when the correct answer would be 1. Can anyone tell me what I am doing
wrong. I am looking for the formula to count the number of times "TRUE" is
found in B2:B6 IF "6N" is also found in A2:A6. Any help would be greatly
appreciated.

SUMPRODUCT((A2:A6="6N")*(B2:B6="TRUE"))

correct answer =1

conditional formula using sumproduct
 

If column B contains only the logical values TRUE or FALSE:

=SUMPRODUCT((A2:A6="6N")*B2:B6)

--
Biff
Microsoft Excel MVP


"ferde" wrote in message
...
A--------------B-
6N FALSE
6N FALSE
7N TRUE
8N TRUE
6N TRUE

I am using this as an array formula in C2 but getting zero for an answer
when the correct answer would be 1. Can anyone tell me what I am doing
wrong. I am looking for the formula to count the number of times "TRUE"
is
found in B2:B6 IF "6N" is also found in A2:A6. Any help would be
greatly
appreciated.

SUMPRODUCT((A2:A6="6N")*(B2:B6="TRUE"))

correct answer =1

conditional formula using sumproduct
 

Ferde,

I dispair that Excel is so anal about data typing. If you have TRUE in a cell, and it's
centered, (presuming you haven't set left, center, or right alignment), then you know that
Excel has typed it as boolean. Your formula won't work because the quotes around "TRUE"
means it's text data type, not boolean. If the TRUE in your cell had been text (such as
with a preceding apostrophe, or having first been formatted for text (before TRUE was
entered into the cell), then your formula would have worked. It's just one more gotcha.

Similar problems occur when Excel has typed a cell as text, but it "looks" like a number.
Happens frequently when numeric data has been pasted in, such as from a web page or other
program. In that case, some formulas will pick up the numbers, and some won't.

The nerds at Excel think users understand data typing, so they designed it with a
programmer's approach, not with a typical end-user's perspective.
--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"ferde" wrote in message
...
A--------------B-
6N FALSE
6N FALSE
7N TRUE
8N TRUE
6N TRUE

I am using this as an array formula in C2 but getting zero for an answer
when the correct answer would be 1. Can anyone tell me what I am doing
wrong. I am looking for the formula to count the number of times "TRUE" is
found in B2:B6 IF "6N" is also found in A2:A6. Any help would be greatly
appreciated.

SUMPRODUCT((A2:A6="6N")*(B2:B6="TRUE"))

correct answer =1

conditional formula using sumproduct
 

Thank you works great

"T. Valko" wrote:

If column B contains only the logical values TRUE or FALSE:

=SUMPRODUCT((A2:A6="6N")*B2:B6)

--
Biff
Microsoft Excel MVP


"ferde" wrote in message
...
A--------------B-
6N FALSE
6N FALSE
7N TRUE
8N TRUE
6N TRUE

I am using this as an array formula in C2 but getting zero for an answer
when the correct answer would be 1. Can anyone tell me what I am doing
wrong. I am looking for the formula to count the number of times "TRUE"
is
found in B2:B6 IF "6N" is also found in A2:A6. Any help would be
greatly
appreciated.

SUMPRODUCT((A2:A6="6N")*(B2:B6="TRUE"))

correct answer =1

conditional formula using sumproduct
 

Thank you very much for your reply

"David Biddulph" wrote:

I guess that your TRUE and FALSE are not text strings but Boolean values.
Try deleting the quote marks around TRUE.

In fact as a Boolean you can delete the =TRUE, so the formula simplifies to
=SUMPRODUCT((A2:A6="6N")*(B2:B6))
--
David Biddulph

"ferde" wrote in message
...
A--------------B-
6N FALSE
6N FALSE
7N TRUE
8N TRUE
6N TRUE

I am using this as an array formula in C2 but getting zero for an answer
when the correct answer would be 1. Can anyone tell me what I am doing
wrong. I am looking for the formula to count the number of times "TRUE"
is
found in B2:B6 IF "6N" is also found in A2:A6. Any help would be
greatly
appreciated.

SUMPRODUCT((A2:A6="6N")*(B2:B6="TRUE"))

correct answer =1

conditional formula using sumproduct
 

I appreciate the explanation that will help me in the future from making
similiar mistakes.

"Earl Kiosterud" wrote:

Ferde,

I dispair that Excel is so anal about data typing. If you have TRUE in a cell, and it's
centered, (presuming you haven't set left, center, or right alignment), then you know that
Excel has typed it as boolean. Your formula won't work because the quotes around "TRUE"
means it's text data type, not boolean. If the TRUE in your cell had been text (such as
with a preceding apostrophe, or having first been formatted for text (before TRUE was
entered into the cell), then your formula would have worked. It's just one more gotcha.

Similar problems occur when Excel has typed a cell as text, but it "looks" like a number.
Happens frequently when numeric data has been pasted in, such as from a web page or other
program. In that case, some formulas will pick up the numbers, and some won't.

The nerds at Excel think users understand data typing, so they designed it with a
programmer's approach, not with a typical end-user's perspective.
--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"ferde" wrote in message
...
A--------------B-
6N FALSE
6N FALSE
7N TRUE
8N TRUE
6N TRUE

I am using this as an array formula in C2 but getting zero for an answer
when the correct answer would be 1. Can anyone tell me what I am doing
wrong. I am looking for the formula to count the number of times "TRUE" is
found in B2:B6 IF "6N" is also found in A2:A6. Any help would be greatly
appreciated.

SUMPRODUCT((A2:A6="6N")*(B2:B6="TRUE"))

correct answer =1

conditional formula using sumproduct
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"ferde" wrote in message
...
Thank you works great

"T. Valko" wrote:

If column B contains only the logical values TRUE or FALSE:

=SUMPRODUCT((A2:A6="6N")*B2:B6)

--
Biff
Microsoft Excel MVP


"ferde" wrote in message
...
A--------------B-
6N FALSE
6N FALSE
7N TRUE
8N TRUE
6N TRUE

I am using this as an array formula in C2 but getting zero for an
answer
when the correct answer would be 1. Can anyone tell me what I am doing
wrong. I am looking for the formula to count the number of times
"TRUE"
is
found in B2:B6 IF "6N" is also found in A2:A6. Any help would be
greatly
appreciated.

SUMPRODUCT((A2:A6="6N")*(B2:B6="TRUE"))

correct answer =1

conditional formula using sumproduct
 

If values in column B are boolean values and not text values, you have to
use
SUMPRODUCT((A2:A6="6N")*(B2:B6=TRUE))
--
Regards.
Daniel
"ferde" a écrit dans le message de news:
...
A--------------B-
6N FALSE
6N FALSE
7N TRUE
8N TRUE
6N TRUE

I am using this as an array formula in C2 but getting zero for an answer
when the correct answer would be 1. Can anyone tell me what I am doing
wrong. I am looking for the formula to count the number of times "TRUE"
is
found in B2:B6 IF "6N" is also found in A2:A6. Any help would be
greatly
appreciated.

SUMPRODUCT((A2:A6="6N")*(B2:B6="TRUE"))

correct answer =1