Count(if) formula help
 

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!

Count(if) formula help
 

=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!

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!

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
...
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!

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
...
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).

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
...
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
...
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).

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
...
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).