Count occurrences based on criteria

Hi There,

I am hoping someone can help me, I have been stumped on this for days now.

I have 273 entries in rows with each of them having a dept code for example 15a, 15b etc.
The dept code is in Coloum A. Colum H tells me if someone has completed their training, if they have completed both parts of their training it says 2 in the cell.

I need a formula that works out how many people are in dept 15a that have a 2 in cell H?

I was using Sumif before but now I am struggling to make it work now that it needs to match "2 "also.

=SUMIF('Training Detail'!$A:$A,$A7,'Training Detail'!$O:$O)

Hope you can help.

Cheers

Originally Posted by daddylonglegs

For a count try using SUMPRODUCT like this

=SUMPRODUCT(('Training Detail'!$A2:$A300=$A7)*('Training Detail'!$H2:$H300=2))

Thats great exactly what I am looking for.

Cheers again

Justin.