Countif: Multiple Columns

  • Thread starter Thread starter bowriter
  • Start date Start date
B

bowriter

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
 
=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.
 
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" <[email protected]> wrote in messagenews:[email protected]...> 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>>
 
Hi David,
You are right I misread the post

David Biddulph said:
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" <[email protected]> wrote in messagenews:[email protected]...> 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
Click to expand...
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>>
 
Back
Top