Quick help using sumproduct to count events of two criteria

[Hollywoood]

Hi Guys,

May just be too early for my brain to be working but I am getting a Div/0 error from the following forumla I am using.

=SUMPRODUCT(--(ISNUMBER(FIND(G19,$C$1:$C$522))),--($D$1:$D$522>0),--($D$1:$D$522<>""))

The goal is to locate the text in cell G19, in the Cells C1-C522 and if that text does exist, to count the occurences where the corresponding cell in column D is not null and is greater than 0.

The above works great if the cell in column D has a numerical value greater than 0 but if it is null or zero, I get a Div/0 error.

Any thoughts on what I may be missing or if my criteria is specified wrong?

Thanks

 

[AlphaFrog]

If you have a #DIV/0! error in any cell that the SUMPRODUCT formula references, that error will be reflected in the SUMPRODUCT formula.

Double check the values in columns C and D

 

[Hollywoood]

I've been trying to pinpoint the issue but its boiled down to there having to be a number greater than 0 in the corresponding cell in column D or the Div/0 happens. I would think my conditions would limit the forumla to ignore null cells or cell values of 0.

 

[AlphaFrog]

Your formula works for me for what it's worth. Can't get it to error using 0 or blanks in column D.

Do you have formulas in columns C or D ?

 

[Hollywoood]

Managed to get it, Thanks Alpha. I wasnt looking to the results of other formulae when the null/0 event went into the cell. Changing the calculations for those eliminated other Div/0 errors in the range as you suggested and it cleared up.

thanks