|
|
|
date: Mon, 6 Oct 2008 12:18:16 -0700,
group: microsoft.public.excel.worksheet.functions
back
RE: Count(if) formula help
For some reason, this doesn't work. I just get a 0 returned.
Initially when I tried this formula, I got #NAME, so i added inverted commas
thus:
=SUMPRODUCT(--(A2:A1000="country"),--(B2:B1000="office type"))
Obviously changing the word "country" to "UK" etc, and "office type" to
"service" etc. to match the list.
Then I took note of your second point about tranges, so changed the formula
thus:
=SUMPRODUCT(--(A2:B1000="country"),--C2:D1000="office type"))
and then tried a third variation on the same theme thus:
{=SUMPRODUCT(--(A2:A1000=country),--(B2:B1000=office type))}
The only thing that ever gets returned is a big fat 0!
Where am I going wrong?
"Sean Timmons" wrote:
> =SUMPRODUCT(--(A2:A1000=country),--(B2:B1000=office type))
>
> Needs to be a trange, not an entire column (Can't type A:A) and both pieces
> must have the same range (2 - 1000 in the above example).
>
> "ELP" wrote:
>
> > I'm trying to count the number of instances a combination of parameters occur.
> > i.e. column a = country, column b = office type.
> > I want to count the number of offices types in each country...
> >
> > Please help!
date: Tue, 7 Oct 2008 23:54:06 -0700
author: ELP
Re: Count(if) formula help
Perhaps your cells contain something different from "UK" and "service"?
Perhaps you have spaces or other non-printing characters? Look at one of
the cells where you think you have "UK" and see whether =LEN(cell_ref) gives
you 2 or not.
--
David Biddulph
"ELP" wrote in message
news:D4BABC46-92FF-4B2B-942D-7A6DE1F3524C@microsoft.com...
> For some reason, this doesn't work. I just get a 0 returned.
>
> Initially when I tried this formula, I got #NAME, so i added inverted
> commas
> thus:
> =SUMPRODUCT(--(A2:A1000="country"),--(B2:B1000="office type"))
> Obviously changing the word "country" to "UK" etc, and "office type" to
> "service" etc. to match the list.
>
> Then I took note of your second point about tranges, so changed the
> formula
> thus:
> =SUMPRODUCT(--(A2:B1000="country"),--C2:D1000="office type"))
> and then tried a third variation on the same theme thus:
> {=SUMPRODUCT(--(A2:A1000=country),--(B2:B1000=office type))}
>
> The only thing that ever gets returned is a big fat 0!
>
> Where am I going wrong?
>
> "Sean Timmons" wrote:
>
>> =SUMPRODUCT(--(A2:A1000=country),--(B2:B1000=office type))
>>
>> Needs to be a trange, not an entire column (Can't type A:A) and both
>> pieces
>> must have the same range (2 - 1000 in the above example).
>>
>> "ELP" wrote:
>>
>> > I'm trying to count the number of instances a combination of parameters
>> > occur.
>> > i.e. column a = country, column b = office type.
>> > I want to count the number of offices types in each country...
>> >
>> > Please help!
date: Wed, 8 Oct 2008 09:00:34 +0100
author: David Biddulph groups [at] biddulph.org.uk
Re: Count(if) formula help
Nope, definitely got the right characters etc. in there. :(
"David Biddulph" wrote:
> Perhaps your cells contain something different from "UK" and "service"?
> Perhaps you have spaces or other non-printing characters? Look at one of
> the cells where you think you have "UK" and see whether =LEN(cell_ref) gives
> you 2 or not.
> --
> David Biddulph
>
> "ELP" wrote in message
> news:D4BABC46-92FF-4B2B-942D-7A6DE1F3524C@microsoft.com...
> > For some reason, this doesn't work. I just get a 0 returned.
> >
> > Initially when I tried this formula, I got #NAME, so i added inverted
> > commas
> > thus:
> > =SUMPRODUCT(--(A2:A1000="country"),--(B2:B1000="office type"))
> > Obviously changing the word "country" to "UK" etc, and "office type" to
> > "service" etc. to match the list.
> >
> > Then I took note of your second point about tranges, so changed the
> > formula
> > thus:
> > =SUMPRODUCT(--(A2:B1000="country"),--C2:D1000="office type"))
> > and then tried a third variation on the same theme thus:
> > {=SUMPRODUCT(--(A2:A1000=country),--(B2:B1000=office type))}
> >
> > The only thing that ever gets returned is a big fat 0!
> >
> > Where am I going wrong?
> >
> > "Sean Timmons" wrote:
> >
> >> =SUMPRODUCT(--(A2:A1000=country),--(B2:B1000=office type))
> >>
> >> Needs to be a trange, not an entire column (Can't type A:A) and both
> >> pieces
> >> must have the same range (2 - 1000 in the above example).
date: Wed, 8 Oct 2008 08:20:01 -0700
author: ELP
Re: Count(if) formula help
Perhaps you've got a typo in your formula? Copy the formula from your
formula bar and paste it to the newsgroup. Don't try to retype it.
If you need to debug further, break things down a stage at a time.
You can check things out with a helper column. In C2, put the formula
=AND(A2="UK",B2="service") and copy down your column and see whether that
gives all FALSE or some TRUE.
If they are all FALSE, go the next stage and put =A2="UK" in D2 and
=B2="service" in E2, and again copy down.
The formula isn't complicated, so your error must be a simple one.
--
David Biddulph
"ELP" wrote in message
news:8A19D23C-261F-4E0E-85D9-A833F5BCBD62@microsoft.com...
> Nope, definitely got the right characters etc. in there. :(
>
> "David Biddulph" wrote:
>
>> Perhaps your cells contain something different from "UK" and "service"?
>> Perhaps you have spaces or other non-printing characters? Look at one of
>> the cells where you think you have "UK" and see whether =LEN(cell_ref)
>> gives
>> you 2 or not.
>> --
>> David Biddulph
>>
>> "ELP" wrote in message
>> news:D4BABC46-92FF-4B2B-942D-7A6DE1F3524C@microsoft.com...
>> > For some reason, this doesn't work. I just get a 0 returned.
>> >
>> > Initially when I tried this formula, I got #NAME, so i added inverted
>> > commas
>> > thus:
>> > =SUMPRODUCT(--(A2:A1000="country"),--(B2:B1000="office type"))
>> > Obviously changing the word "country" to "UK" etc, and "office type" to
>> > "service" etc. to match the list.
>> >
>> > Then I took note of your second point about tranges, so changed the
>> > formula
>> > thus:
>> > =SUMPRODUCT(--(A2:B1000="country"),--C2:D1000="office type"))
>> > and then tried a third variation on the same theme thus:
>> > {=SUMPRODUCT(--(A2:A1000=country),--(B2:B1000=office type))}
>> >
>> > The only thing that ever gets returned is a big fat 0!
>> >
>> > Where am I going wrong?
>> >
>> > "Sean Timmons" wrote:
>> >
>> >> =SUMPRODUCT(--(A2:A1000=country),--(B2:B1000=office type))
>> >>
>> >> Needs to be a trange, not an entire column (Can't type A:A) and both
>> >> pieces
>> >> must have the same range (2 - 1000 in the above example).
date: Wed, 8 Oct 2008 17:54:29 +0100
author: David Biddulph groups [at] biddulph.org.uk
Re: Count(if) formula help
Noticing one of yours shows
=SUMPRODUCT(--(A2:B1000="country"),--C2:D1000="office type"))
should have been:
=SUMPRODUCT(--(A2:B1000="UK"),--(C2:D1000="service"))
The {} one would indicate an array. Not needed for this issue.
Hoping it was something as simple as that! :-)
"ELP" wrote:
> Nope, definitely got the right characters etc. in there. :(
>
> "David Biddulph" wrote:
>
> > Perhaps your cells contain something different from "UK" and "service"?
> > Perhaps you have spaces or other non-printing characters? Look at one of
> > the cells where you think you have "UK" and see whether =LEN(cell_ref) gives
> > you 2 or not.
> > --
> > David Biddulph
> >
> > "ELP" wrote in message
> > news:D4BABC46-92FF-4B2B-942D-7A6DE1F3524C@microsoft.com...
> > > For some reason, this doesn't work. I just get a 0 returned.
> > >
> > > Initially when I tried this formula, I got #NAME, so i added inverted
> > > commas
> > > thus:
> > > =SUMPRODUCT(--(A2:A1000="country"),--(B2:B1000="office type"))
> > > Obviously changing the word "country" to "UK" etc, and "office type" to
> > > "service" etc. to match the list.
> > >
> > > Then I took note of your second point about tranges, so changed the
> > > formula
> > > thus:
> > > =SUMPRODUCT(--(A2:B1000="country"),--C2:D1000="office type"))
> > > and then tried a third variation on the same theme thus:
> > > {=SUMPRODUCT(--(A2:A1000=country),--(B2:B1000=office type))}
> > >
> > > The only thing that ever gets returned is a big fat 0!
> > >
> > > Where am I going wrong?
> > >
> > > "Sean Timmons" wrote:
> > >
> > >> =SUMPRODUCT(--(A2:A1000=country),--(B2:B1000=office type))
> > >>
> > >> Needs to be a trange, not an entire column (Can't type A:A) and both
> > >> pieces
> > >> must have the same range (2 - 1000 in the above example).
date: Wed, 8 Oct 2008 14:37:01 -0700
author: Sean Timmons
|
|
