Vlookup to count non blank cells

[jen2018]

Hello

Can anyone tell me what formula I need please?

I have 2 tables, both have our sites listed in column A. I want the second
table to count how many non blank cells there are in column B for each site
in table 1.

The tables look somthing like this:

Table 1:

Site 1 Data
Site 2 Data
Site 2 Data
Site 2
Site 3
Site 4 Data
Site 4
Site 4

Table 2:

Site 1
Site 2
Site 3
Site 4

 

[Mike H]

Hi,

Lets say your second table is in A12 down. Put this in B12 and drag down

=SUMPRODUCT(($A$1:$A$9=A12)*($B$1:$B$9<>""))

Mike

 

[jen2018]

Thanks but the data is text, not figures so it doesn't work, also the 2
tables are on separate spreadsheets. Is there another way?

 

[Mike H]

Hi,

Why do you suppose my formula doesn't work on text? Did you try it?

This now assumes your data are on sheet2 and the second table is on another
sheet with the sites names in A1 down.

=SUMPRODUCT((Sheet2!$A$1:$A$9=A1)*(Sheet2!$B$1:$B$9<>""))

Mike

 

[jen2018]

Thanks Mike

I did try it but must've done something wrong as it came back with an error,
all working now though!

 

[Mike H]

Glad I could help

Thanks Mike

I did try it but must've done something wrong as it came back with an error,
all working now though!