Function Help!!

[Gadgetman]

I need help with a worksheet function for number of accidents that
have certain catagories assigned.

Cells in Column B of my worksheet are to contain either "Reportable" -
or- "Non-
Reportable".
Cells in Column C are to contain a number with either "EK" -or- "EA"
attached at the end (for example: 0140-11/EK or 4562-11/EA)


I need a formula/function that calculates how many accidents that are
marked:
1 - Just "Reportable" AND have the "EK" designations.
2. Just "Reportable" AND "EA"
3. Just "Non-Reportable" AND "EK"
4. Just "Non-Reportable" AND "EA"

The worksheet is too large to count them by
eye so a formula would be great. If anyone can help, I'd appreciate
it.


Thanks

 

[Ian S]

I need help with a worksheet function for number of accidents that
have certain catagories assigned.

Cells in Column B of my worksheet are to contain either "Reportable" -
or- "Non-
Reportable".
Cells in Column C are to contain a number with either "EK" -or- "EA"
attached at the end (for example: 0140-11/EK or 4562-11/EA)

I need a formula/function that calculates how many accidents that are
marked:
1 - Just "Reportable" AND have the "EK" designations.
2. Just "Reportable" AND "EA"
3. Just "Non-Reportable" AND "EK"
4. Just "Non-Reportable" AND "EA"

The worksheet is too large to count them by
eye so a formula would be great. If anyone can help, I'd appreciate
it.

Thanks

This can be done with the following steps:

1. Have your data start in row 3.

2. Set up a column D to show designation: =RIGHT(C3,2) will return the
EA or EK designation, without the numbers in front of them.

4. Set up columns E to H, one for each of your four classes you
described above. Put "Reportable" in E1, F1, and "Non-Reportable" in
G1, H1. Put "EK" in E2, G2, and "EA" in F2, H2.

5. Set up a nested "IF" statement in cell E3: =IF($B3=E$1,IF($D3=E
$2,1,0),0). Copy this for all rows of the table, columns E thru H.

6. Total each column E thru H, and you have your totals as you
requested. You can use these totals elsewhere if you want, and group
columns E through H to hide them for presentation purposes if you need
to.

Good luck!



I will email you a sample.