Countif: Multiple Columns
Hi folks,
I have two ranges and am trying to count how many times "0" occurs simultaneously in both. Example: A B C X 0 0 Y 3 1 Z 5 0 XX 0 0 The countif should come back as "2" (2 values [x and xx] share 0 in common) I tried =Countif(B2:B100, "0") + Countif(C2:C100, "0"). That didn't work even though one would think "+" should be an "and" and not an "or" (Excel treated "+" as an "or" so that in the above example the result came back as "3") I've read in other help sites a pivot table would be the best tool to use, but I really didn't want to get that complicated...it's a pretty simple spredsheet. Thanks for any help, R.E. Kelly |
Countif: Multiple Columns
=SUMPRODUCT((B1:B4=0)*(C1:C4=0))
would work. Though because it's specifically 0, you need to ensure the sumproduct only contains the actual rows of data, else it will also count blanks as 0. "bowriter" wrote: Hi folks, I have two ranges and am trying to count how many times "0" occurs simultaneously in both. Example: A B C X 0 0 Y 3 1 Z 5 0 XX 0 0 The countif should come back as "2" (2 values [x and xx] share 0 in common) I tried =Countif(B2:B100, "0") + Countif(C2:C100, "0"). That didn't work even though one would think "+" should be an "and" and not an "or" (Excel treated "+" as an "or" so that in the above example the result came back as "3") I've read in other help sites a pivot table would be the best tool to use, but I really didn't want to get that complicated...it's a pretty simple spredsheet. Thanks for any help, R.E. Kelly |
Countif: Multiple Columns
Hi,
use =SUMPRODUCT(($B$6:$B$10=0)+($C$6:$C$10=0)) "bowriter" wrote: Hi folks, I have two ranges and am trying to count how many times "0" occurs simultaneously in both. Example: A B C X 0 0 Y 3 1 Z 5 0 XX 0 0 The countif should come back as "2" (2 values [x and xx] share 0 in common) I tried =Countif(B2:B100, "0") + Countif(C2:C100, "0"). That didn't work even though one would think "+" should be an "and" and not an "or" (Excel treated "+" as an "or" so that in the above example the result came back as "3") I've read in other help sites a pivot table would be the best tool to use, but I really didn't want to get that complicated...it's a pretty simple spredsheet. Thanks for any help, R.E. Kelly |
Countif: Multiple Columns
What answer does your formula give for the OP's example, Eduardo?
I think it gives 7, and he said he wanted the answer to be 2. =SUMPRODUCT(($B$6:$B$10=0)*($B$6:$B$10<"")*($C$6: $C$10=0)*($C$6:$C$10<""))would tackle two problems:Firstly that your formula does an OR when I think you need an AND (which waswhat the OP was pointing out).Secondly that I assume that the OP didn't want blanks treated as zeroes.--David Biddulph"Eduardo" wrote in ... Hi, use =SUMPRODUCT(($B$6:$B$10=0)+($C$6:$C$10=0)) "bowriter" wrote: Hi folks, I have two ranges and am trying to count how many times "0" occurs simultaneously in both. Example: A B C X 0 0 Y 3 1 Z 5 0 XX 0 0 The countif should come back as "2" (2 values [x and xx] share 0 in common) I tried =Countif(B2:B100, "0") + Countif(C2:C100, "0"). That didn't work even though one would think "+" should be an "and" and not an "or" (Excel treated "+" as an "or" so that in the above example the result came back as "3") I've read in other help sites a pivot table would be the best tool to use, but I really didn't want to get that complicated...it's a pretty simple spredsheet. Thanks for any help, R.E. Kelly |
Countif: Multiple Columns
Hi David,
You are right I misread the post "David Biddulph" wrote: What answer does your formula give for the OP's example, Eduardo? I think it gives 7, and he said he wanted the answer to be 2. =SUMPRODUCT(($B$6:$B$10=0)*($B$6:$B$10<"")*($C$6: $C$10=0)*($C$6:$C$10<""))would tackle two problems:Firstly that your formula does an OR when I think you need an AND (which waswhat the OP was pointing out).Secondly that I assume that the OP didn't want blanks treated as zeroes.--David Biddulph"Eduardo" wrote in ... Hi, use =SUMPRODUCT(($B$6:$B$10=0)+($C$6:$C$10=0)) "bowriter" wrote: Hi folks, I have two ranges and am trying to count how many times "0" occurs simultaneously in both. Example: A B C X 0 0 Y 3 1 Z 5 0 XX 0 0 The countif should come back as "2" (2 values [x and xx] share 0 in common) I tried =Countif(B2:B100, "0") + Countif(C2:C100, "0"). That didn't work even though one would think "+" should be an "and" and not an "or" (Excel treated "+" as an "or" so that in the above example the result came back as "3") I've read in other help sites a pivot table would be the best tool to use, but I really didn't want to get that complicated...it's a pretty simple spredsheet. Thanks for any help, R.E. Kelly |
All times are GMT +1. The time now is 03:58 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com