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
>

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" <Eduardo DeleteThis @discussions.microsoft.com> wrote in messagenews:4C52A0C8-57D0-4363-8EAE-669CCD8CE2B3@microsoft.com...> 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" 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" <Eduardo.RemoveThis@discussions.microsoft.com> wrote in messagenews:4C52A0C8-57D0-4363-8EAE-669CCD8CE2B3@microsoft.com...> 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>>
>
>