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" <(E-Mail Removed)> wrote in message
news:EC30D22A-8CE9-4F5B-960A-(E-Mail Removed)...
> 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

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!
=====================================================

"PSmith" <(E-Mail Removed)> wrote in message
news:EC30D22A-8CE9-4F5B-960A-(E-Mail Removed)...
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
news:eh$(E-Mail Removed)...
"PSmith" <(E-Mail Removed)> wrote in message
news:EC30D22A-8CE9-4F5B-960A-(E-Mail Removed)...
> 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

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" wrote:

> "PSmith" <(E-Mail Removed)> wrote in message
> news:EC30D22A-8CE9-4F5B-960A-(E-Mail Removed)...
> > 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
>
> 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.
>
>
>

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" wrote:

> 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!
> =====================================================
>
> "PSmith" <(E-Mail Removed)> wrote in message
> news:EC30D22A-8CE9-4F5B-960A-(E-Mail Removed)...
> 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
>
>
>

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" <(E-Mail Removed)> wrote in message
news:268BB171-8DC1-4BCD-8529-(E-Mail Removed)...
> 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" wrote:
>
>> 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!
>> =====================================================
>>
>> "PSmith" <(E-Mail Removed)> wrote in message
>> news:EC30D22A-8CE9-4F5B-960A-(E-Mail Removed)...
>> 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
>>
>>
>>