How to count multiple conditions that are "TRUE"

P

PSmith

Hi;

I am attempting to adapt an existing formula [=COUNTIF(AO$2:AO$2093,TRUE)]so
that it further differentiates between those that are also "TRUE" in another
range (AD2:AD2093), versus those who are "FALSE". Any suggestions would be
greatly appreciated,

Peter
 
S

Stephen

PSmith said:
Hi;

I am attempting to adapt an existing formula
[=COUNTIF(AO$2:AO$2093,TRUE)]so
that it further differentiates between those that are also "TRUE" in
another
range (AD2:AD2093), versus those who are "FALSE". Any suggestions would be
greatly appreciated,

Peter
Click to expand...

SUMPRODUCT is a more versatile function than SUMIF or COUNTIF.
The equivalent for your COUNTIF formula is:
=SUMPRODUCT(--(AO$2:AO$2093))
Then to add in a second condition is simple:
=SUMPRODUCT(--(AO$2:AO$2093),--(AD2:AD2093))
You can easily add further conditions if required in the future.
 
R

RagDyeR

If I understand you correctly, this should work:

=SUMPRODUCT((AD2:AD2093=TRUE)*(AO2:AO2093=TRUE))


--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Hi;

I am attempting to adapt an existing formula [=COUNTIF(AO$2:AO$2093,TRUE)]so
that it further differentiates between those that are also "TRUE" in another
range (AD2:AD2093), versus those who are "FALSE". Any suggestions would be
greatly appreciated,

Peter
 
R

RagDyeR

The caveat with your formula is that it works with logicals only.

Any text in the referenced columns will cause an error.

However, that may *not* be a problem, depending on the OP's data.

Just an FYI for the OP.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Stephen" <none> wrote in message
PSmith said:
Hi;

I am attempting to adapt an existing formula
[=COUNTIF(AO$2:AO$2093,TRUE)]so
that it further differentiates between those that are also "TRUE" in
another
range (AD2:AD2093), versus those who are "FALSE". Any suggestions would be
greatly appreciated,

Peter
Click to expand...

SUMPRODUCT is a more versatile function than SUMIF or COUNTIF.
The equivalent for your COUNTIF formula is:
=SUMPRODUCT(--(AO$2:AO$2093))
Then to add in a second condition is simple:
=SUMPRODUCT(--(AO$2:AO$2093),--(AD2:AD2093))
You can easily add further conditions if required in the future.
 
P

PSmith

Just a question about your solution: which part of the formula specifies the
"TRUE" aspect?

Stephen said:
PSmith said:
Hi;

I am attempting to adapt an existing formula
[=COUNTIF(AO$2:AO$2093,TRUE)]so
that it further differentiates between those that are also "TRUE" in
another
range (AD2:AD2093), versus those who are "FALSE". Any suggestions would be
greatly appreciated,

Peter
Click to expand...

SUMPRODUCT is a more versatile function than SUMIF or COUNTIF.
The equivalent for your COUNTIF formula is:
=SUMPRODUCT(--(AO$2:AO$2093))
Then to add in a second condition is simple:
=SUMPRODUCT(--(AO$2:AO$2093),--(AD2:AD2093))
You can easily add further conditions if required in the future.
Click to expand...
 
P

PSmith

Thanks RagDyer; I had used quotation marks around the words TRUE, and when
that didn't work, I tried (,TRUE). Thanks very much for your help, it is very
much appreciated.
 
R

RagDyer

You're very welcome, and thank you for the feed back.

As to your question to Stephen about his formula:
If the *only* values in both your columns are TRUE and/or FALSE, it will
work.

If there's a possibility that those columns might contain numbers, then you
might have incorrect results returned (paired rows will multiply each
other).
If there's text in either column, then you'll get a #Value error.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
PSmith said:
Thanks RagDyer; I had used quotation marks around the words TRUE, and when
that didn't work, I tried (,TRUE). Thanks very much for your help, it is
very
much appreciated.

RagDyeR said:
If I understand you correctly, this should work:

=SUMPRODUCT((AD2:AD2093=TRUE)*(AO2:AO2093=TRUE))


--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Hi;

I am attempting to adapt an existing formula
[=COUNTIF(AO$2:AO$2093,TRUE)]so
that it further differentiates between those that are also "TRUE" in
another
range (AD2:AD2093), versus those who are "FALSE". Any suggestions would
be
greatly appreciated,

Peter
Click to expand...
Click to expand...
 
Top