How to count multiple conditions that are "TRUE"

  • Thread starter Thread starter PSmith
  • Start date Start date
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
 
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.
 
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
 
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.
 
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...
 
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.
 
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...
 
Back
Top