Nesting COUNTIF

TheMSsForum.com: The Microsoft Software Forum

In Excel 2003, is there a way to combine the functions COUNTIF(A1:A10,"Joe")
and COUNTIF(B1:B10,">5")? In other words, the array in Cols A and B has
scores from 0 to 10 for multiple trials by Joe, Bill, and Jane; I want a
count of all of Joe's scores that are over 5.
Top

Re: Nesting COUNTIF by Sandy

Sandy
Sat Mar 15 13:29:09 CDT 2008

=SUMPRODUCT((A1:A10="Joe")*(B1:B10>5))

If you meant 5 or over then use >=5

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"Andrew K" <AndrewK@discussions.microsoft.com> wrote in message
news:6140A0CD-CF3D-4F0C-98EC-CF1EEB124A6E@microsoft.com...
> In Excel 2003, is there a way to combine the functions
> COUNTIF(A1:A10,"Joe")
> and COUNTIF(B1:B10,">5")? In other words, the array in Cols A and B has
> scores from 0 to 10 for multiple trials by Joe, Bill, and Jane; I want a
> count of all of Joe's scores that are over 5.
>
>


Top

Re: Nesting COUNTIF by AndrewK

AndrewK
Tue Mar 18 09:11:00 CDT 2008

Works perfectly. Thank you.

"Sandy Mann" wrote:

> =SUMPRODUCT((A1:A10="Joe")*(B1:B10>5))
>
> If you meant 5 or over then use >=5
>
> --
> HTH
>
> Sandy
> In Perth, the ancient capital of Scotland
> and the crowning place of kings
>
> sandymann2@mailinator.com
> Replace @mailinator.com with @tiscali.co.uk
>
>
> "Andrew K" <AndrewK@discussions.microsoft.com> wrote in message
> news:6140A0CD-CF3D-4F0C-98EC-CF1EEB124A6E@microsoft.com...
> > In Excel 2003, is there a way to combine the functions
> > COUNTIF(A1:A10,"Joe")
> > and COUNTIF(B1:B10,">5")? In other words, the array in Cols A and B has
> > scores from 0 to 10 for multiple trials by Joe, Bill, and Jane; I want a
> > count of all of Joe's scores that are over 5.
> >
> >
>
>
>
Top

Re: Nesting COUNTIF by Sandy

Sandy
Tue Mar 18 09:17:03 CDT 2008

You're very welcome. Thanks for the feedback.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"Andrew K" <AndrewK@discussions.microsoft.com> wrote in message
news:1E6B3D17-34D2-4419-A445-92F7E9F73237@microsoft.com...
> Works perfectly. Thank you.
>
> "Sandy Mann" wrote:
>
>> =SUMPRODUCT((A1:A10="Joe")*(B1:B10>5))
>>
>> If you meant 5 or over then use >=5
>>
>> --
>> HTH
>>
>> Sandy
>> In Perth, the ancient capital of Scotland
>> and the crowning place of kings
>>
>> sandymann2@mailinator.com
>> Replace @mailinator.com with @tiscali.co.uk
>>
>>
>> "Andrew K" <AndrewK@discussions.microsoft.com> wrote in message
>> news:6140A0CD-CF3D-4F0C-98EC-CF1EEB124A6E@microsoft.com...
>> > In Excel 2003, is there a way to combine the functions
>> > COUNTIF(A1:A10,"Joe")
>> > and COUNTIF(B1:B10,">5")? In other words, the array in Cols A and B
>> > has
>> > scores from 0 to 10 for multiple trials by Joe, Bill, and Jane; I want
>> > a
>> > count of all of Joe's scores that are over 5.
>> >
>> >
>>
>>
>>
>


Top