I work for a transportation company. On a spreadsheet, I have a list of
Shippers, beside that, I have a list of unique SKUs that the coresponding
shipper carried. Beside that, I have the coresponding Bill of Lading (BOL)
that the Carrier took that SKU out on. I need to count how many times a
particular shipper had a unique BOL.

In the example below, I need the formula to return that there were 2 BOLs
associated with AVRT. I don't need to know what numbers they are or what SKUs
were on them, but I need to know how many shipments there were.

Shipper SKU BOL
AVRT asd 123
AVRT sfg 123
GST dgh 345
JBH ert 876
GST sdf 645
AVRT ghj 875
AVRT skr 875

Thank You!

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

E2 = AVRT

=SUM(--(FREQUENCY(IF(A2:A8=E2,MATCH(C2:C8,C2:C8,0)),MATCH (C2:C8,C2:C8,0))0))

Biff

"Averitt Engineer" wrote in
message ...
I work for a transportation company. On a spreadsheet, I have a list of
Shippers, beside that, I have a list of unique SKUs that the coresponding
shipper carried. Beside that, I have the coresponding Bill of Lading (BOL)
that the Carrier took that SKU out on. I need to count how many times a
particular shipper had a unique BOL.

In the example below, I need the formula to return that there were 2 BOLs
associated with AVRT. I don't need to know what numbers they are or what
SKUs
were on them, but I need to know how many shipments there were.

Shipper SKU BOL
AVRT asd 123
AVRT sfg 123
GST dgh 345
JBH ert 876
GST sdf 645
AVRT ghj 875
AVRT skr 875

Thank You!

"T. Valko" wrote...
Entered as an array using the key combination of CTRL,SHIFT,ENTER
(not just ENTER):

E2 = AVRT

=SUM(--(FREQUENCY(IF(A2:A8=E2,MATCH(C2:C8,C2:C8,0)),
MATCH(C2:C8,C2:C8,0))0))
....

Maybe, but a simpler, nonarray formula would also work:

=SUMPRODUCT((A2:A8=E2)/COUNTIF(C2:C8,C2:C8))

This would only break down if the col C values could start with
comparison operators (<, =, ) or include wildcard characters.
Actually, wildcard characters could also screw up the MATCH calls.

Regarding:
SUMPRODUCT((A2:A8=E2)/COUNTIF(C2:C8,C2:C8))

Possible issue if the same Bill of Lading ref could exist for more than one
shipper. Not enough information to determine if there's any exposure on that,
though.

***********
Regards,
Ron

XL2002, WinXP


"Harlan Grove" wrote:

"T. Valko" wrote...
Entered as an array using the key combination of CTRL,SHIFT,ENTER
(not just ENTER):

E2 = AVRT

=SUM(--(FREQUENCY(IF(A2:A8=E2,MATCH(C2:C8,C2:C8,0)),
MATCH(C2:C8,C2:C8,0))0))
....

Maybe, but a simpler, nonarray formula would also work:

=SUMPRODUCT((A2:A8=E2)/COUNTIF(C2:C8,C2:C8))

This would only break down if the col C values could start with
comparison operators (<, =, ) or include wildcard characters.
Actually, wildcard characters could also screw up the MATCH calls.

Ron Coderre wrote...
Regarding:
SUMPRODUCT((A2:A8=E2)/COUNTIF(C2:C8,C2:C8))

Possible issue if the same Bill of Lading ref could exist for more
than one shipper. Not enough information to determine if there's
any exposure on that, though.
....

It gets more complicated and requires a longer formula.

=SUM(IF(A2:A8=E2,1/MMULT(TRANSPOSE(A2:A8=E2)
*(C2:C8=TRANSPOSE(C2:C8)),ROW(C2:C8)^0)))

The advantage of this formula is that it works when col C values could
contain wildcards. But maybe overkill. My main objection to Biff's
formula was multiple MATCH calls, but they could be reduced.

=COUNT(1/FREQUENCY(IF(A2:A8=E2,MATCH(C2:C8,C2:C8,0)),
ROW(C2:C8)-MIN(ROW(C2:C8))+1))

Ok...

I've abandoned my use of SUM(..MATCH...MATCH) in favor
of COUNT(..MATCH...ROW)

Biff

"Harlan Grove" wrote in message
oups.com...
Ron Coderre wrote...
Regarding:
SUMPRODUCT((A2:A8=E2)/COUNTIF(C2:C8,C2:C8))

Possible issue if the same Bill of Lading ref could exist for more
than one shipper. Not enough information to determine if there's
any exposure on that, though.
...

It gets more complicated and requires a longer formula.

=SUM(IF(A2:A8=E2,1/MMULT(TRANSPOSE(A2:A8=E2)
*(C2:C8=TRANSPOSE(C2:C8)),ROW(C2:C8)^0)))

The advantage of this formula is that it works when col C values could
contain wildcards. But maybe overkill. My main objection to Biff's
formula was multiple MATCH calls, but they could be reduced.

=COUNT(1/FREQUENCY(IF(A2:A8=E2,MATCH(C2:C8,C2:C8,0)),
ROW(C2:C8)-MIN(ROW(C2:C8))+1))

There can never be a BOL on more than one carrier.

Thanks for the help.

"Ron Coderre" wrote:

Regarding:
SUMPRODUCT((A2:A8=E2)/COUNTIF(C2:C8,C2:C8))

Possible issue if the same Bill of Lading ref could exist for more than one
shipper. Not enough information to determine if there's any exposure on that,
though.

***********
Regards,
Ron

XL2002, WinXP


"Harlan Grove" wrote:

"T. Valko" wrote...
Entered as an array using the key combination of CTRL,SHIFT,ENTER
(not just ENTER):

E2 = AVRT

=SUM(--(FREQUENCY(IF(A2:A8=E2,MATCH(C2:C8,C2:C8,0)),
MATCH(C2:C8,C2:C8,0))0))
....

Maybe, but a simpler, nonarray formula would also work:

=SUMPRODUCT((A2:A8=E2)/COUNTIF(C2:C8,C2:C8))

This would only break down if the col C values could start with
comparison operators (<, =, ) or include wildcard characters.
Actually, wildcard characters could also screw up the MATCH calls.

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook

=ArrayCount(ArrayUniques(VLookups("AVRT",A1:C8,3)) )

Alan Beban

Averitt Engineer wrote:
I work for a transportation company. On a spreadsheet, I have a list of
Shippers, beside that, I have a list of unique SKUs that the coresponding
shipper carried. Beside that, I have the coresponding Bill of Lading (BOL)
that the Carrier took that SKU out on. I need to count how many times a
particular shipper had a unique BOL.

In the example below, I need the formula to return that there were 2 BOLs
associated with AVRT. I don't need to know what numbers they are or what SKUs
were on them, but I need to know how many shipments there were.

Shipper SKU BOL
AVRT asd 123
AVRT sfg 123
GST dgh 345
JBH ert 876
GST sdf 645
AVRT ghj 875
AVRT skr 875

Thank You!

Alan Beban <unavailable wrote...
If the functions in the freely downloadable file at
....

=ArrayCount(ArrayUniques(VLookups("AVRT",A1:C8,3) ))
....

Better idea for the OP would be using Laurent Longre's MOREFUNC.XLL
add-in, available from

http://xcell05.free.fr/english/

then using the formula

=COUNTDIFF(IF(A2:A8=E2,C2:C8),,FALSE)

I am trying to get your formula to work, but I keep getting 'FALSE.' This is
my formula

=COUNTDIFF(IF(log!R4:R65536=BC3,log!T4:T65536),,FA LSE)

And this is a sample of my columns


(col R) (col T)
Carrier BOL #

GIST (BAH) 104502
GIST (BAH) 104502
GIST (BAH) 104502
AVRT 104501
AVRT 104501
AVRT 104501

I would like to know that there is one AVRT BOL in that list. Why is this
returning 'FALSE'?

Thanks so much for the help.









"Harlan Grove" wrote:

Alan Beban <unavailable wrote...
If the functions in the freely downloadable file at
....

=ArrayCount(ArrayUniques(VLookups("AVRT",A1:C8,3) ))
....

Better idea for the OP would be using Laurent Longre's MOREFUNC.XLL
add-in, available from

http://xcell05.free.fr/english/

then using the formula

=COUNTDIFF(IF(A2:A8=E2,C2:C8),,FALSE)

Averitt Engineer wrote...
I am trying to get your formula to work, but I keep getting 'FALSE.'
This is my formula

=COUNTDIFF(IF(log!R4:R65536=BC3,log!T4:T65536),,F ALSE)

I can't get COUNTDIFF to return FALSE. Zero (0), yes. FALSE, no.

And this is a sample of my columns

(col R) (col T)
Carrier BOL #

GIST (BAH) 104502
GIST (BAH) 104502
GIST (BAH) 104502
AVRT 104501
AVRT 104501
AVRT 104501
....

Given the above (including the column headings and blank row) in R1:T8
(with col S blank), and with AVRT in cell W1 rather than BC3, the
array formula

=COUNTDIFF(IF(R3:R8=W1,T3:T8),,FALSE)

returns 1 on my system. What version of MOREFUNC.XLL are you using?
Also, if you don't enter the formula as an array formula, it'll return
#VALUE!. Is that what you mean by FALSE? Also, it's possible there are
stray trailing spaces in either your col R values or your BC3 value.
You could try the array formula

=COUNTDIFF(IF(TRIM(log!R4:R65536)=TRIM(BC3),log!T4 :T65536),,FALSE)

ArrayCount and ArrayUnique don't seem to be functions that I have???

"Alan Beban" wrote:

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook

=ArrayCount(ArrayUniques(VLookups("AVRT",A1:C8,3)) )

Alan Beban

Averitt Engineer wrote:
I work for a transportation company. On a spreadsheet, I have a list of
Shippers, beside that, I have a list of unique SKUs that the coresponding
shipper carried. Beside that, I have the coresponding Bill of Lading (BOL)
that the Carrier took that SKU out on. I need to count how many times a
particular shipper had a unique BOL.

In the example below, I need the formula to return that there were 2 BOLs
associated with AVRT. I don't need to know what numbers they are or what SKUs
were on them, but I need to know how many shipments there were.

Shipper SKU BOL
AVRT asd 123
AVRT sfg 123
GST dgh 345
JBH ert 876
GST sdf 645
AVRT ghj 875
AVRT skr 875

Thank You!

Nevermind. I get it. Thanks!

"Averitt Engineer" wrote:

ArrayCount and ArrayUnique don't seem to be functions that I have???

"Alan Beban" wrote:

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook

=ArrayCount(ArrayUniques(VLookups("AVRT",A1:C8,3)) )

Alan Beban

Averitt Engineer wrote:
I work for a transportation company. On a spreadsheet, I have a list of
Shippers, beside that, I have a list of unique SKUs that the coresponding
shipper carried. Beside that, I have the coresponding Bill of Lading (BOL)
that the Carrier took that SKU out on. I need to count how many times a
particular shipper had a unique BOL.

In the example below, I need the formula to return that there were 2 BOLs
associated with AVRT. I don't need to know what numbers they are or what SKUs
were on them, but I need to know how many shipments there were.

Shipper SKU BOL
AVRT asd 123
AVRT sfg 123
GST dgh 345
JBH ert 876
GST sdf 645
AVRT ghj 875
AVRT skr 875

Thank You!

With
your posted table of data in A1:C10 (I added some blank cells)
AND
the assumption that BOL cells will be either numbers or numeric text (123 or
023)

Then if
E1: AVRT

this regular formula returns the count of UNIQUE BOLs for AVRT
F1: =SUMPRODUCT(--(FREQUENCY((A2:A10=E1)*C2:C10,(A2:A10=E1)*C2:C10) 0))-1

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Averitt Engineer" wrote:

I work for a transportation company. On a spreadsheet, I have a list of
Shippers, beside that, I have a list of unique SKUs that the coresponding
shipper carried. Beside that, I have the coresponding Bill of Lading (BOL)
that the Carrier took that SKU out on. I need to count how many times a
particular shipper had a unique BOL.

In the example below, I need the formula to return that there were 2 BOLs
associated with AVRT. I don't need to know what numbers they are or what SKUs
were on them, but I need to know how many shipments there were.

Shipper SKU BOL
AVRT asd 123
AVRT sfg 123
GST dgh 345
JBH ert 876
GST sdf 645
AVRT ghj 875
AVRT skr 875

Thank You!

Assuming that A2:C8 contains the data, and E2 contains AVRT, try the
following formula which needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF(FREQUENCY(IF($A$2:$A$8=E2,IF($C$2:$C$8<"" ,MATCH("~"&$C$2:$C$8,$C
$2:$C$8&"",0))),ROW($C$2:$C$8)-ROW($C$2)+1),1))

Note that Column C can contain numerical values, text values,
empty/blank cells, comparison characters, and wildcard characters.

Hope this helps!

In article ,
Averitt Engineer wrote:

I work for a transportation company. On a spreadsheet, I have a list of
Shippers, beside that, I have a list of unique SKUs that the coresponding
shipper carried. Beside that, I have the coresponding Bill of Lading (BOL)
that the Carrier took that SKU out on. I need to count how many times a
particular shipper had a unique BOL.

In the example below, I need the formula to return that there were 2 BOLs
associated with AVRT. I don't need to know what numbers they are or what SKUs
were on them, but I need to know how many shipments there were.

Shipper SKU BOL
AVRT asd 123
AVRT sfg 123
GST dgh 345
JBH ert 876
GST sdf 645
AVRT ghj 875
AVRT skr 875

Thank You!

Note that Column C can contain numerical values, text values,
empty/blank cells, comparison characters, and wildcard characters.

That's just about everything and the kitchen sink! But........what about
error values and booleans?

<VBG

Biff

"Domenic" wrote in message
...
Assuming that A2:C8 contains the data, and E2 contains AVRT, try the
following formula which needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF(FREQUENCY(IF($A$2:$A$8=E2,IF($C$2:$C$8<"" ,MATCH("~"&$C$2:$C$8,$C
$2:$C$8&"",0))),ROW($C$2:$C$8)-ROW($C$2)+1),1))

Note that Column C can contain numerical values, text values,
empty/blank cells, comparison characters, and wildcard characters.

Hope this helps!

In article ,
Averitt Engineer wrote:

I work for a transportation company. On a spreadsheet, I have a list of
Shippers, beside that, I have a list of unique SKUs that the coresponding
shipper carried. Beside that, I have the coresponding Bill of Lading
(BOL)
that the Carrier took that SKU out on. I need to count how many times a
particular shipper had a unique BOL.

In the example below, I need the formula to return that there were 2 BOLs
associated with AVRT. I don't need to know what numbers they are or what
SKUs
were on them, but I need to know how many shipments there were.

Shipper SKU BOL
AVRT asd 123
AVRT sfg 123
GST dgh 345
JBH ert 876
GST sdf 645
AVRT ghj 875
AVRT skr 875

Thank You!

In article ,
"T. Valko" wrote:

That's just about everything and the kitchen sink! But........what about
error values and booleans?

<VBG

Biff

I guess not quite everything... <VBG

Hello,

I suggest a careful step by step approach:

If your data is in Sheet1, A1:C8:
1. Create a helper column in sheet1. Enter
=A1&";"&C1
into D1 and copy down to D8

2. Get my UDF List_Freq from http://www.sulprobil.com/html/listfreq.html
and insert it into a macro module:
Press ALT + F11, insert a module, copy macro text into module, go back
to worksheet, but now Sheet2 (TWO!)

3. Select cells A1:B8 in sheet2 and array-enter
=List_Freq(Sheet1!D1:D8,1)
[Press CTRL + ALT + ENTER to enter formula, curly brackets should
enclose your formula after that]

4. Create a helper column in sheet2. Enter
=LEFT(FIND(";",A1&";")-1)
into C1 and copy down to C8

5. Select cells A1:B8 in sheet3 and array-enter
=List_Freq(Sheet1!C1:C8,1)
[Press CTRL + ALT + ENTER to enter formula, curly brackets should
enclose your formula after that]

Sheet3 shows your results. Ignore blank and zero entries in first
rows.

I hope it is an advantage to see intermediate results here. You can
spot user entry errors easily, I think.

Regards,
Bernd