(Msg. 1) Posted: Mon Aug 18, 2008 2:53 pm
Post subject: Help with a sum formula Archived from groups: microsoft>public>excel>worksheet>functions (more info?)
I am having trouble getting the right number from a formula and I was
wondering if anyone can help me correct my error.
In column B I have a mix of names that I am only trying to pick one out of
such as
B5 contains Joe, Paul, John, etc
And column E contains Yes or No
I need to count how many times a name appears with a yes but having multiple
names in the cell is throwing it off. The formula I am trying to use is-
=SUM((B5:B320="*Paul*")*(E5:E320="Yes"))
and then hitting ctrl, shift, enter to put it in array
It is returning a 0, which is incorrect. If I try the formula with only one
name in a box and get rid of the ** it works fine. Any Ideas? Any help is
appreciated. Thanks.
--
Michael
(Msg. 2) Posted: Mon Aug 18, 2008 4:57 pm
Post subject: Re: Help with a sum formula [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Try
=SUMproduct((B5:B320="Paul")*(E5:E320="Yes"))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1.RemoveThis@austin.rr.com
"Michael" <Michael.RemoveThis@discussions.microsoft.com> wrote in message
news:216DF252-4A0F-479C-9DCD-47944FEC6DED@microsoft.com...
>I am having trouble getting the right number from a formula and I was
> wondering if anyone can help me correct my error.
>
> In column B I have a mix of names that I am only trying to pick one out of
> such as
>
> B5 contains Joe, Paul, John, etc
>
> And column E contains Yes or No
>
> I need to count how many times a name appears with a yes but having
> multiple
> names in the cell is throwing it off. The formula I am trying to use is-
>
> =SUM((B5:B320="*Paul*")*(E5:E320="Yes"))
>
> and then hitting ctrl, shift, enter to put it in array
> It is returning a 0, which is incorrect. If I try the formula with only
> one
> name in a box and get rid of the ** it works fine. Any Ideas? Any help is
> appreciated. Thanks.
> --
> Michael
(Msg. 3) Posted: Mon Aug 18, 2008 5:14 pm
Post subject: Re: Help with a sum formula [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
On Aug 18, 10:53 pm, Michael <Mich....DeleteThis@discussions.microsoft.com>
wrote:
> I am having trouble getting the right number from a formula and I was
> wondering if anyone can help me correct my error.
>
> In column B I have a mix of names that I am only trying to pick one out of
> such as
>
> B5 contains Joe, Paul, John, etc
>
> And column E contains Yes or No
>
> I need to count how many times a name appears with a yes but having multiple
> names in the cell is throwing it off. The formula I am trying to use is-
>
> =SUM((B5:B320="*Paul*")*(E5:E320="Yes"))
>
> and then hitting ctrl, shift, enter to put it in array
> It is returning a 0, which is incorrect. If I try the formula with only one
> name in a box and get rid of the ** it works fine. Any Ideas? Any help is
> appreciated. Thanks.
> --
> Michael
(Msg. 4) Posted: Tue Aug 19, 2008 4:12 pm
Post subject: Re: Help with a sum formula [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Thanks. That worked great.
--
Michael
"Pete_UK" wrote:
> Try this:
>
> =SUMPRODUCT((ISNUMBER(SEARCH("Paul",B5:B320)))*(E5:E320="Yes"))
>
> Hope this helps.
>
> Pete
>
> On Aug 18, 10:53 pm, Michael <Mich... DeleteThis @discussions.microsoft.com>
> wrote:
> > I am having trouble getting the right number from a formula and I was
> > wondering if anyone can help me correct my error.
> >
> > In column B I have a mix of names that I am only trying to pick one out of
> > such as
> >
> > B5 contains Joe, Paul, John, etc
> >
> > And column E contains Yes or No
> >
> > I need to count how many times a name appears with a yes but having multiple
> > names in the cell is throwing it off. The formula I am trying to use is-
> >
> > =SUM((B5:B320="*Paul*")*(E5:E320="Yes"))
> >
> > and then hitting ctrl, shift, enter to put it in array
> > It is returning a 0, which is incorrect. If I try the formula with only one
> > name in a box and get rid of the ** it works fine. Any Ideas? Any help is
> > appreciated. Thanks.
> > --
> > Michael
>
>
All times are: Eastern Time (US & Canada) (change)
Page 1 of 1
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
Office