SUMPRODUCT help

[handerson0603]

I have a spreadsheet that tabulates test results for a given month on a day-by-day basis. It was written using array formulas involving nested IF functions, and I am trying to rewrite the formulas using SUMPRODUCT.

The challenge I am finding is in matching partial cell contents. The error codes in my raw data are the same every time, but the error message includes the value that was out of spec, so the text of the error messages is almost never exactly the same twice. I need to count how many of each error occurred on each day of the month.

The array formula that is currently accomplishing this task is:

{=SUM(IF(daterange=A1,IF(ISERR(SEARCH(B1,resultsrange)),0,1),0),0)}

Is there a way to do this with SUMPRODUCT?

[daddylonglegs]

You can do it with SUMPRODUCT but it's preferable to use COUNTIFS with a wildcard in Excel 2007 probably, try

=COUNTIFS(daterange,A1,resultsrange,"*"&B1&"*")

edit: SUMPRODUCT version would be something like this

=SUMPRODUCT((daterange=A1)*ISNUMBER(SEARCH(B1,resultsrange)))