Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count from Blank & Non-Blank Cells | Excel Discussion (Misc queries) | |||
Count the non blank cells | Excel Worksheet Functions | |||
Help getting count of non-blank cells | Excel Discussion (Misc queries) | |||
Do Not count blank cells | Excel Discussion (Misc queries) | |||
COUNT ONLY CELLS THAT AREN'T BLANK | Excel Worksheet Functions |