Prev: Open/Import MS Money OFX file into Excel?
Next: Open WB, copy each sh. & paste into other WB, regardless of tab na
From: Wombat on 29 Jan 2010 05:35
Right!

column A contains area codes (two digit numbers, eg. 30), column B customer
numbers (eg 999999)

The lists then go down listing orders. I want to count how many different
customers have placed an order per area (not counting duplicates)

Thanks for any help
From: Jacob Skaria on 29 Jan 2010 05:45
With the query area code in cell C1 enter the below formula. Please note that
this is an array formula. You create array formulas in the same way that you
create other formulas, except you press CTRL+SHIFT+ENTER to enter the
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula>}"

=SUM(IF(FREQUENCY(IF(A1:A100=C1,MATCH(B1:B100,B1:B100,0)),
ROW(B1:B100)-ROW(A1)+1),1))

--
Jacob


"Wombat" wrote:

> Right!
>
> column A contains area codes (two digit numbers, eg. 30), column B customer
> numbers (eg 999999)
>
> The lists then go down listing orders. I want to count how many different
> customers have placed an order per area (not counting duplicates)
>
> Thanks for any help
From: מיכאל (מיקי) אבידן on 29 Jan 2010 05:58
You may also check [I did not] the Array-Formula:
{=COUNT(1/FREQUENCY(IF($A$1:$A$100=C1,$B$1:$B$100),$B$1:$B$100))}
Micky


"Wombat" wrote:

> Right!
>
> column A contains area codes (two digit numbers, eg. 30), column B customer
> numbers (eg 999999)
>
> The lists then go down listing orders. I want to count how many different
> customers have placed an order per area (not counting duplicates)
>
> Thanks for any help
 | 
Pages: 1
Prev: Open/Import MS Money OFX file into Excel?
Next: Open WB, copy each sh. & paste into other WB, regardless of tab na