Dear Expert
I am a new to excel and i need to calculate the fill rate using the below
data.
My data is large , est around 2000 SKU lines
I try search from the post here, but no luck.
Appreciate your expertise.

Data Sample

Plate No. Date Ord.Ord No. SKU. Supp.
ABC123,12/1/08,123,Code1,Yes
ABC123,12/01/08,123,Code2,No
ABC124,13/01/08,125,Code3,Yes
ABC124,13/01/08,125,Code4,Yes
ABC125,13/01/08,126,Code5,Yes

I wish to get the answer how Plate No. with full parts supplied (yes) in
this case Ans: is 2 (ABC124 and ABC125) In addtion i may need to select by
date range.
Thank You
Mike

You'll need to provide more information. Why isn't the answer 3? ABC123 has
"Yes" in the row.

Regards,
Fred

"Mike JF" wrote in message
...
Dear Expert
I am a new to excel and i need to calculate the fill rate using the below
data.
My data is large , est around 2000 SKU lines
I try search from the post here, but no luck.
Appreciate your expertise.

Data Sample

Plate No. Date Ord.Ord No. SKU. Supp.
ABC123,12/1/08,123,Code1,Yes
ABC123,12/01/08,123,Code2,No
ABC124,13/01/08,125,Code3,Yes
ABC124,13/01/08,125,Code4,Yes
ABC125,13/01/08,126,Code5,Yes

I wish to get the answer how Plate No. with full parts supplied (yes) in
this case Ans: is 2 (ABC124 and ABC125) In addtion i may need to select by
date range.
Thank You
Mike

Hi Fred
apologize for not been clear.
The reason because ABC123 require 2 lines of SKU, but only one SKU is
supllied,
so ABC123 is not counted as "Yes".

In summary, if one vehicle require 10 lines of SKU and all 10 lines SKU are
supplied, then status is "yes"
Hope i explained well.
Thank You

"Fred Smith" wrote:

You'll need to provide more information. Why isn't the answer 3? ABC123 has
"Yes" in the row.

Regards,
Fred

"Mike JF" wrote in message
...
Dear Expert
I am a new to excel and i need to calculate the fill rate using the below
data.
My data is large , est around 2000 SKU lines
I try search from the post here, but no luck.
Appreciate your expertise.

Data Sample

Plate No. Date Ord.Ord No. SKU. Supp.
ABC123,12/1/08,123,Code1,Yes
ABC123,12/01/08,123,Code2,No
ABC124,13/01/08,125,Code3,Yes
ABC124,13/01/08,125,Code4,Yes
ABC125,13/01/08,126,Code5,Yes

I wish to get the answer how Plate No. with full parts supplied (yes) in
this case Ans: is 2 (ABC124 and ABC125) In addtion i may need to select by
date range.
Thank You
Mike






.

Hi,

Try this approach.

1. Highlight the range (incl. header row) go to Data Filter Advanced
Filter
2. Select Copy to another location
3. In the list box, select D4:E9 (row 4 is the header row)
4. Leave criteria blank
5. In the copy to box, select any one blank cell, say I5
6. Check the box for unique records only
7. Assume that the output (incl. the header row) is in range I5:J9
8. Now in cell E14, type condition and in cell F14, type the heading of the
first column I.e. Plate No.
9. In cell E15, type
=COUNTIF($I$6:$I$9,I6)=SUMPRODUCT(($I$6:$I$9=I6)*( $J$6:$J$9="Yes"))
10. In cell F15, type =DCOUNTA(I5:J9,F14,E14:E15). This should get the
answer

Hope this helps.
--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Mike JF" wrote in message
...
Dear Expert
I am a new to excel and i need to calculate the fill rate using the below
data.
My data is large , est around 2000 SKU lines
I try search from the post here, but no luck.
Appreciate your expertise.

Data Sample

Plate No. Date Ord.Ord No. SKU. Supp.
ABC123,12/1/08,123,Code1,Yes
ABC123,12/01/08,123,Code2,No
ABC124,13/01/08,125,Code3,Yes
ABC124,13/01/08,125,Code4,Yes
ABC125,13/01/08,126,Code5,Yes

I wish to get the answer how Plate No. with full parts supplied (yes) in
this case Ans: is 2 (ABC124 and ABC125) In addtion i may need to select by
date range.
Thank You
Mike

Hi Mathur,
I followed your instruction but not getting the answer. In cell e15, i shows
false, and in cell f15 shows 0. The correct answer shld be 2. In additional i
also need to select by date range.

btw i notice the last part of this array (J6:J9="yes"), but actually these
range (j6:J9) are dates in the sheet
=COUNTIF($I$6:$I$9,I6)=SUMPRODUCT(($I$6:$I$9=I6)*( $J$6:$J$9="Yes"))

Thank





"Ashish Mathur" wrote:

Hi,

Try this approach.

1. Highlight the range (incl. header row) go to Data Filter Advanced
Filter
2. Select Copy to another location
3. In the list box, select D4:E9 (row 4 is the header row)
4. Leave criteria blank
5. In the copy to box, select any one blank cell, say I5
6. Check the box for unique records only
7. Assume that the output (incl. the header row) is in range I5:J9
8. Now in cell E14, type condition and in cell F14, type the heading of the
first column I.e. Plate No.
9. In cell E15, type
=COUNTIF($I$6:$I$9,I6)=SUMPRODUCT(($I$6:$I$9=I6)*( $J$6:$J$9="Yes"))
10. In cell F15, type =DCOUNTA(I5:J9,F14,E14:E15). This should get the
answer

Hope this helps.
--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Mike JF" wrote in message
...
Dear Expert
I am a new to excel and i need to calculate the fill rate using the below
data.
My data is large , est around 2000 SKU lines
I try search from the post here, but no luck.
Appreciate your expertise.

Data Sample

Plate No. Date Ord.Ord No. SKU. Supp.
ABC123,12/1/08,123,Code1,Yes
ABC123,12/01/08,123,Code2,No
ABC124,13/01/08,125,Code3,Yes
ABC124,13/01/08,125,Code4,Yes
ABC125,13/01/08,126,Code5,Yes

I wish to get the answer how Plate No. with full parts supplied (yes) in
this case Ans: is 2 (ABC124 and ABC125) In addtion i may need to select by
date range.
Thank You
Mike

Hi,

Changed the reference to the column which has Yes I.e. if Yes, No appears in
column L, then in the sumproduct formula, the range should be
($L$6:$L$9="Yes")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Mike JF" wrote in message
...
Hi Mathur,
I followed your instruction but not getting the answer. In cell e15, i
shows
false, and in cell f15 shows 0. The correct answer shld be 2. In
additional i
also need to select by date range.

btw i notice the last part of this array (J6:J9="yes"), but actually these
range (j6:J9) are dates in the sheet
=COUNTIF($I$6:$I$9,I6)=SUMPRODUCT(($I$6:$I$9=I6)*( $J$6:$J$9="Yes"))

Thank





"Ashish Mathur" wrote:

Hi,

Try this approach.

1. Highlight the range (incl. header row) go to Data Filter Advanced
Filter
2. Select Copy to another location
3. In the list box, select D4:E9 (row 4 is the header row)
4. Leave criteria blank
5. In the copy to box, select any one blank cell, say I5
6. Check the box for unique records only
7. Assume that the output (incl. the header row) is in range I5:J9
8. Now in cell E14, type condition and in cell F14, type the heading of
the
first column I.e. Plate No.
9. In cell E15, type
=COUNTIF($I$6:$I$9,I6)=SUMPRODUCT(($I$6:$I$9=I6)*( $J$6:$J$9="Yes"))
10. In cell F15, type =DCOUNTA(I5:J9,F14,E14:E15). This should get the
answer

Hope this helps.
--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Mike JF" wrote in message
...
Dear Expert
I am a new to excel and i need to calculate the fill rate using the
below
data.
My data is large , est around 2000 SKU lines
I try search from the post here, but no luck.
Appreciate your expertise.

Data Sample

Plate No. Date Ord.Ord No. SKU. Supp.
ABC123,12/1/08,123,Code1,Yes
ABC123,12/01/08,123,Code2,No
ABC124,13/01/08,125,Code3,Yes
ABC124,13/01/08,125,Code4,Yes
ABC125,13/01/08,126,Code5,Yes

I wish to get the answer how Plate No. with full parts supplied (yes)
in
this case Ans: is 2 (ABC124 and ABC125) In addtion i may need to select
by
date range.
Thank You
Mike