ExcelBanter - sumproduct exclude count

thanks that work fine, I see I was half way there.
thanks again

"Toppers" wrote:

TRY:

=SUMPRODUCT(--(A7:A60="test"))-SUMPRODUCT(--(A7:A60="test")*(B7:B60={"Problem","Solved"}))

"Dreamstar_1961" wrote:

I'm trying to get somproduct to count every occurance of a word but except
where they match another word. like below it would not count both "problem"
and "solved" but would count every thing else, but it don't seem to be
working, this is how I thought it be written
=sumproduct((a7:a60="test")-(b7:b60={"problem","solved"}))
given a result for below of 3, but that is not working as I seem to have a
ghost of 1 coming up,
a7 test b7problem
a8 test b8 solved
a9 test
a10 test b10 resloved
a11 test b11 next