Countblank in B if criteria in A?

[JessicaK]

Can anyone tell me how to count blanks in column B if they are next to criteria in column A?

PERSON 1 BLAH
PERSON 1
PERSON 1
PERSON 1 BLAH
PERSON 2
PERSON 2 BLAH
PERSON 2 BLAH
PERSON 2

I think I want something like if A:A = Person 1, then countblank in B:B?

[NBVC]

Try something like:

=SUMPRODUCT((A1:A8="Person 1")*(B1:B8=""))

adjust ranges to suit

[JessicaK]

Thanks but that just comes up #VALUE!

[NBVC]

Are there any errors in the ranges you're looking at?

[JessicaK]

No, at the moment they're exactly the same as in my first post.

[NBVC]

Can you do me a favour and attach your sheet then?

[JessicaK]

Sure

and more text so I can post

[NBVC]

Not sure what you were doing to get that error.. but it worked for me...

[JessicaK]

Neither do I...but thanks!

[JessicaK]

Actually I have another problem. I need this to reference A:A and B:B because the sheet will be constantly updated, and now it has names instead of person 1, person 2 etc. The formula doesn't work when I change it? Sorry, I don't know much about excel...

[NBVC]

Attach a sample workbook showing what you mean.

[JessicaK]

Oops, sorry.

[NBVC]

This doesn't work in I6, copied down?

=SUMPRODUCT(($E$6:$E$31=H6)*($F$6:$F$31=""))

[JessicaK]

I need E:E and F:F, when I enter that it doesn't work.

[NBVC]

No that doesn't work... you are correct....

Excel 2003 and earlier don't allow full column references like E:E... Excel 2007 does....

You can use the entire column range -1 though.

e.g.
=SUMPRODUCT(($E$1:$E$65535=H6)*($F$1:$F$65535="")) or

=SUMPRODUCT(($E$2:$E$65536=H6)*($F$2:$F$65536=""))

[JessicaK]

Brilliant, thanks.

[Yaristiz]

Hi, Why use SUMPRODUCT instead of COUNTIFS?

Thanks!

PS. I'm a total Rookie trying to write Macros mostly with Record Macro and adding minor edits.

[Pepe Le Mokko]

When the OP started the thread there was no indication as to which version of XL he was using. To play it safe Vittorio used SUMPRODUCT.

This being said, have a look at forum rules about hijacking threads. Although it is not strictly your case, we don't want to grow bad habits, don't we?

[Yaristiz]

Thanks Pepe Le Mokko. Sorry that my reply was delayed, vacation time .