My data is arranged as:
a b c d .......
aa8 ATWIL HOM 45.87 ...
aa8 ATWIL AUT 34.54 ...
aa8 ATWIL DFW 26.75 ...
bb9 BBQRE PKG 222.76 ...


I have already been able to sum the d column by column A in sheet 2. Now I
want to count the different codes in column B by column A. The key is I
don't want to count everything, but how many different codes there are in
column B for a particular code in column A.

All help is greatly appreciated.

Thanks

Cathy

use the count if function with the match function:

=SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2: B10,B2:B10,0))0,1))
this example must be entered as array formulas (array formula: A formula
that performs multiple calculations on one or more sets of values, and then
returns either a single result or multiple results. Array formulas are
enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.).
Select each cell that contains a formula, press F2, and then press
CTRL+SHIFT+ENTER.




If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"cware" wrote:

My data is arranged as:
a b c d .......
aa8 ATWIL HOM 45.87 ...
aa8 ATWIL AUT 34.54 ...
aa8 ATWIL DFW 26.75 ...
bb9 BBQRE PKG 222.76 ...


I have already been able to sum the d column by column A in sheet 2. Now I
want to count the different codes in column B by column A. The key is I
don't want to count everything, but how many different codes there are in
column B for a particular code in column A.

All help is greatly appreciated.

Thanks

Cathy

I'm going to have to figure out the array thing because on first couple of
tries, this is not working for me. I'll try again later this week...thanks

"Michael" wrote:

use the count if function with the match function:

=SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2: B10,B2:B10,0))0,1))
this example must be entered as array formulas (array formula: A formula
that performs multiple calculations on one or more sets of values, and then
returns either a single result or multiple results. Array formulas are
enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.).
Select each cell that contains a formula, press F2, and then press
CTRL+SHIFT+ENTER.




If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"cware" wrote:

My data is arranged as:
a b c d .......
aa8 ATWIL HOM 45.87 ...
aa8 ATWIL AUT 34.54 ...
aa8 ATWIL DFW 26.75 ...
bb9 BBQRE PKG 222.76 ...


I have already been able to sum the d column by column A in sheet 2. Now I
want to count the different codes in column B by column A. The key is I
don't want to count everything, but how many different codes there are in
column B for a particular code in column A.

All help is greatly appreciated.

Thanks

Cathy