Sum product formula.

[arnab0711]

Hi,
I have this excel workbook where in there is a Snap Shot sheet,where we have to provide the count of distributor billed for a product,where zone =" ",month=" "
for that I have written a formula,=(SUMPRODUCT((raws!$B$5:$B$2000=$C$2)*(raws!$A$5:$A$2000=$C$3)*(raws!$E$5:$E$2000=$B7)*(raws!$F$5:$F$2000=1)))
which works perfectly fine for all distributors who have billed during that month.But my requirement is the count of distributors who haven't billed during that month.

[Cutter]

First off, I see that you have array entered your SUMPRODUCT() formulas. That isn't necessary. They just need to be confirmed with the Enter key. The first set of brackets is also not necessary, so the formula would be:
=SUMPRODUCT((raws!$B$5:$B$2000=$C$2)*(raws!$A$5:$A$2000=$C$3)*(raws!$E$5:$E$2000=$B7)*(raws !$F$5:$F$2000=1))

Now, the best part - you don't need SUMPRODUCT() at all - you can use COUNTIFS() which is more efficient. Use this in cell Snap shot!C5:
=COUNTIFS(raws!$B$5:$B$2000,$C$2,raws!$A$5:$A$2000,C$3,raws!$E$5:$E$2000,$B5,raws!$F$5:$F$2000,1) and drag down

And as for your question, what determines a distributor who hasn't billed? Would it be simply 'not a 1' in column F? If so, then this in D5
=COUNTIFS(raws!$B$5:$B$2000,$C$2,raws!$A$5:$A$2000,C$3,raws!$E$5:$E$2000,$B5,raws!$F$5:$F$2000,"<>1") and drag down