Adding =countif to an =sumproduct range (Maybe)

I am having an issue with my formula. =SUMPRODUCT((A2:A2000=D3)*(B2:B2000)) The formula works great except I need to add countif greater than 0 to this and I can't figure out how. The issue is (Attached), that in cell D3 if you have nothing entered (This is what the user would search by), it (E3) will count every customer with a 0 ship to in column A. I want to ignore those. Also, the info that is in columns A & B will be added from another program (iSeries navigator). These numbers are not manually entered. Only D3 would be for the user.

Thanks for any help,

Jay

So would you want to see a total of 8 if a zero is actually entered in D3? Assuming so then you can use an IF function to return a blank when D3 is blank....and you probably only need SUMIF, like this

=IF(D3="","",SUMIF(A:A,D3,B:B))

=SUMPRODUCT((A2:A2000=D3)*(A2:A2000<>0)*(B2:B2000)) maybe

Originally Posted by jayclinton

that in cell D3 if you have nothing entered (This is what the user would search by), it (E3) will count every customer with a 0 ship to in column A. I want to ignore those
Jay

I'm a little confused, if there's nothing in D3, ie blank are you counting that in the same way as 0? if you want to ignore 0 and blank =SUMPRODUCT((A2:A2000=D3)*(A2:A2000<>0)*(A2:A2000<>"")*(B2:B2000)) but if you want to keep the option of summing if the 0 is entered

=SUMPRODUCT((A2:A2000=D3)*(A2:A2000<>"")*(B2:B2000))