Vlookup to count non blank cells
 

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

Vlookup to count non blank cells
 

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" wrote:

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

Vlookup to count non blank cells
 

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" wrote:

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" wrote:

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

Vlookup to count non blank cells
 

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" wrote:

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" wrote:

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" wrote:

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

Vlookup to count non blank cells
 

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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

Vlookup to count non blank cells
 

Glad I could help

"jen2018" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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