Counting blank cells based on non adjacent criteria

[FrankMcG]

Excel Example.jpgI've tried pretty hard to find this answer before posting and it seems lots have asked a similar question but not exactly my situation. From what I can tell SUMPRODUCT is the answer to many people's questions but I can't make it go in this application. This is for Excel 2010.

See screen shot below (if it came through). Example when the analyst assigned column (I) reads "smith" I need excel to count the blank cells in that row for columns A,B,C,D. In the shortened example below I would want the function to return "8" in cell L3. The formula would be modified to show the same results for "Jones", "Dell" etc.

I have used "IF(I3:I13="Smith",COUNTBLANK(A3:D3),"")" - and this gives the correct results for ONE row -but when this is expanded to include the full range it does not work. I can see a crude way of doing this with an extra sheet and dragging the formula down but it seems Excel would be able to do this without all that mess. Where am I going wrong?

Excel Example.jpg

[Pepe Le Mokko]

Hi
please post a sample sheet. Pictures are nice but useless

[newdoverman]

Here is one way that the blanks can be counted.

[FrankMcG]

Excel Forum Example.xlsxI obvioulsy need to get more comfortable with the SUMPRODUCT function. Newdover I can see how I can apply what you have and that will do the job. Thank you!

Since Pepe asked I also attached a shortened version of what I'm working with, if anybody wants to have another go at it.

[newdoverman]

You're welcome and thanks for the feedback.

[Tony Valko]

Here is one way that the blanks can be counted.

This array formula** entered in L3 and copied down:

=SUM(IF(I$3:I$13=K3,IF(A$3:D$13="",1)))


** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.