I have data that increases on a daily basis and is input on the shop
floor from a drop-down list. Some of the data that is collected
includes day, the type of failure and the corresponding part that
failed.

For example:
COL_A COL_J COL_H
Date Type of Failure Part # Failed
10/20/2005 Supplier – Porosity 1357 – Gasket
10/20/2005 Internal – Cracked 5678 – O-Ring
10/20/2005 Internal – Split 2345 – Seal
10/20/2005 Internal – Pinched 1234 – Seal
10/20/2005 Internal – Pinched 1234 – Seal
10/20/2005 Internal – Pinched 1234 – Seal
10/20/2005 Internal – Pinched 1234 – Seal
10/21/2005 Internal – Pinched 1234 - Seal
10/21/2005 Internal – Cracked 5678 – O-Ring
10/21/2005 Internal – Cracked 5678 – O-Ring
10/21/2005 Internal – Cracked 5678 – O-Ring
10/21/2005 Internal – Cracked 5678 – O-Ring

I somehow need to develop a formula that will count the types of
failures and the corresponding part number that failed by the day it
occurred. I can do a COUNTIF but it cannot do all that is asked. I also
tried to filter by date then use COUNTIF, but it still looks at all data
in the column.

Can someone point me in the right direction?


--
Racorfrank
------------------------------------------------------------------------
Racorfrank's Profile: http://www.excelforum.com/member.php...o&userid=17882
View this thread: http://www.excelforum.com/showthread...hreadid=479556

Hi!

COL_A COL_J COL_H
Date Type of Failure Part # Failed
10/20/2005 Supplier - Porosity 1357 - Gasket

Other than the date, what data is in what column? It looks like the date is
in column A.

What's in column J? Supplier? Supplier - Porosity?

Biff

"Racorfrank" wrote
in message ...

I have data that increases on a daily basis and is input on the shop
floor from a drop-down list. Some of the data that is collected
includes day, the type of failure and the corresponding part that
failed.

For example:
COL_A COL_J COL_H
Date Type of Failure Part # Failed
10/20/2005 Supplier - Porosity 1357 - Gasket
10/20/2005 Internal - Cracked 5678 - O-Ring
10/20/2005 Internal - Split 2345 - Seal
10/20/2005 Internal - Pinched 1234 - Seal
10/20/2005 Internal - Pinched 1234 - Seal
10/20/2005 Internal - Pinched 1234 - Seal
10/20/2005 Internal - Pinched 1234 - Seal
10/21/2005 Internal - Pinched 1234 - Seal
10/21/2005 Internal - Cracked 5678 - O-Ring
10/21/2005 Internal - Cracked 5678 - O-Ring
10/21/2005 Internal - Cracked 5678 - O-Ring
10/21/2005 Internal - Cracked 5678 - O-Ring

I somehow need to develop a formula that will count the types of
failures and the corresponding part number that failed by the day it
occurred. I can do a COUNTIF but it cannot do all that is asked. I also
tried to filter by date then use COUNTIF, but it still looks at all data
in the column.

Can someone point me in the right direction?


--
Racorfrank
------------------------------------------------------------------------
Racorfrank's Profile:
http://www.excelforum.com/member.php...o&userid=17882
View this thread: http://www.excelforum.com/showthread...hreadid=479556

Biff,

Col A contains the "Date" of repair (from a drop down box)

Col J contains the "Type of Failure" (from a drop down box), i.e.
Supplier - Porosity

Col H contains the "Part # Failed" (from a drop down box), i.e. 31496 -
O-Ring

Thanks for your interest!

Frank


--
Racorfrank
------------------------------------------------------------------------
Racorfrank's Profile: http://www.excelforum.com/member.php...o&userid=17882
View this thread: http://www.excelforum.com/showthread...hreadid=479556

Hi!

Table:

.....................A....................H....... .............J.............
1...............Date...............Part #...........Failure Type
2.........10/20/2005.......1234 - Seal......Internal - Pinched

OK!

For the criteria, do you want to count the Part # by using the entire part #
and nomenclatu 1234 - Seal

Or, can you count by using ONLY the numerical portion of the part #: 1234

Same thing for the Failure Type:

Do you want to use the entire description: Internal - Pinched

Or, can you use just Pinched or just Internal?

Based on the table you posted being in the range A1:J13 with the headers in
row 1 and the data starting in row 2:

This example will use just the numeric portion of the part # and just the
"Pinched" portion of the failure type:

So, to count the number of entries for 10/20/2005 with a part # of 1234 and
a failure type of pinched:

Use cells to hold the criteria:

A15 = Date = 10/20/2005
B15 = Part # = 1234
C15 = Failure Type = pinched

=SUMPRODUCT(--(A2:A13=A15),--(ISNUMBER(FIND(B15,H2:H13))),--(ISNUMBER(SEARCH(C15,J2:J13))))

Biff

"Racorfrank" wrote
in message ...

Biff,

Col A contains the "Date" of repair (from a drop down box)

Col J contains the "Type of Failure" (from a drop down box), i.e.
Supplier - Porosity

Col H contains the "Part # Failed" (from a drop down box), i.e. 31496 -
O-Ring

Thanks for your interest!

Frank


--
Racorfrank
------------------------------------------------------------------------
Racorfrank's Profile:
http://www.excelforum.com/member.php...o&userid=17882
View this thread: http://www.excelforum.com/showthread...hreadid=479556