Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count multiple conditions that are "TRUE"
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count multiple conditions that are "TRUE"
"PSmith" wrote in message
... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count multiple conditions that are "TRUE"
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count multiple conditions that are "TRUE"
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" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count multiple conditions that are "TRUE"
Just a question about your solution: which part of the formula specifies the
"TRUE" aspect? "Stephen" wrote: "PSmith" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count multiple conditions that are "TRUE"
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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count multiple conditions that are "TRUE"
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" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check if cells contain the word "Thailand", return "TRUE" | Excel Worksheet Functions | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Typing "true" excel 2007 change it to "TRUE" | Excel Discussion (Misc queries) | |||
conditions and count occurrences of "not blank" | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |