Count number of times a vlookup/match formula is empty

[Remaniak]

Hi All,

I wish to do the following:

I am in sheet "Analysis" and I have a cell (B2) with the value "germany" and a cell (G2) with the value "freight".

What I want to know is how many of the cells in sheet "data" are empty within the column with the header "freight", where in column A the value is "Germany".

I used =Countblank(VLOOKUP(B2;'data'!$A:$A;MATCH(G2;'data'!$1:$1;0);0))

Which does not work.

Does anyone have a none VB solution?

Thanks

Remaniak

 

[Aladin Akyurek]

Hi All,

I wish to do the following:

I am in sheet "Analysis" and I have a cell (B2) with the value "germany" and a cell (G2) with the value "freight".

What I want to know is how many of the cells in sheet "data" are empty within the column with the header "freight", where in column A the value is "Germany".

I used =Countblank(VLOOKUP(B2;'data'!$A:$A;MATCH(G2;'data'!$1:$1;0);0))

Which does not work.

Does anyone have a none VB solution?

Thanks

Remaniak

=SUMPRODUCT(--(data!$A$2:$A$100=B2);--(INDEX(data!$B$2:$F$100;0;MATCH(G2;data!$B$1:$F$1;0))=""))

Adjust the ranges to suit, but no whole columns on versions prior to 2007.

 

[Remaniak]

Awesome!

Thanks a lot!

Remaniak