Count(if) formula help

[ELP]

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!

 

[Sean Timmons]

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

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"),--C21000="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?

 

[David Biddulph]

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.

 

[ELP]

Nope, definitely got the right characters etc. in there.

 

[David Biddulph]

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.

 

[Sean Timmons]

Noticing one of yours shows

=SUMPRODUCT(--(A2:B1000="country"),--C21000="office type"))

should have been:

=SUMPRODUCT(--(A2:B1000="UK"),--(C21000="service"))

The {} one would indicate an array. Not needed for this issue.

Hoping it was something as simple as that!