Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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). |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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). |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Formula - Count Ticks | Excel Discussion (Misc queries) | |||
Count formula | Excel Discussion (Misc queries) | |||
is there a formula to count something like this.... | Excel Worksheet Functions | |||
Trying to construct a count count formula | Excel Discussion (Misc queries) | |||
Count formula Help... | Excel Discussion (Misc queries) |