VLOOKUP and COUNTIF together?

Hi,

Please can someone help me with a problem that I have with a formula.

My boss has asked me to work this one out and is putting me under great pressure to resolve it. I have tried Vlookup and COUNTIF etc but just cannot get to make it work.

I have attached an example file but to explain:

I have a database of inspectors faults, in column B, I have the week number, column*C, I have the part number & column C the fault category (this includes OK). The actual database is up to 5000 rows now.

What I need to be able to do is in one cell (column H2) count all the OK's for week 1 and all the Not OK's (column I2) in week 1 and the same for week 2 (column H3) and week 3 (column H4) and so on for the whole year. I have tried a vlookup using cell G2 (containing "1") as the search and the week number column as the range and then counting if OK, but it never worked!

Any help would be gratefully appreciated and you will be saving me from the wrath of my boss ;-)


Wrekin

In H2: =SUMPRODUCT(--($B$2:$B$58=$G2),--($D$2:$D$58="OK"))

in I2: =SUMPRODUCT(--($B$2:$B$58=$G2),--($D$2:$D$58<>"OK"))

adjust the ranges to suit your data and then copy both down the columns.

In XL2007, these should also work

=COUNTIFS($B$2:$B$58,$G2,$D$2:$D$58,"OK")

=COUNTIFS($B$2:$B$58,$G2,$D$2:$D$58,"<>OK")