Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Like Sumproduct() but using values from a list
Hi!
I have the following problem In 2 worksheets i have the following In the 1st one i have 99 columns full with values from 0 to 5. On top of each column (A2, B2, C2 ....) there is a text numbe (01,02,03,...,99). These text numbers using to make the columns to b grouped. So i 've got 10 columns with the header '01', 20 columns with the header '02', 5 columns with the header '03', 14 columns with the header '05' and so on. On the other worksheet i have 5 columns. In each column i count th values of 0 to 5. So in the column A i get the count of 'how many 0 are in the columns o the previous worksheet' in the column B i get the count of 'how many 1 are in the columns o the previous worksheet' in the column E i get the count of 'how many 5 are in the columns o the previous worksheet' What i want is: I use a list under the cell 'G1' on the 2nd worksheet. The lis contains the text numbers from 01 to 99 from the 1st worksheet. I wan each time a choose a value from the list eg. the '01' all the column from the 1st worksheet which their header matches to '01' to count th 0 to 5 and the result to be on the 2nd worksheet. The following formula doing what i want but its extremely SLOW and i dont know how to put the value from the list. SUMPRODUCT((1st!$A$2:$CU$2='01')*(1st!$A3:$CU500=0 )) Thanks in advance Stathi -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Like Sumproduct() but using values from a list
Hi
try: SUMPRODUCT((1st!$A$2:$CU$2=G1)*(1st!$A3:$CU500=0)) or if the apostrophes are also part of the header but nor part of your listvalues: SUMPRODUCT((1st!$A$2:$CU$2="'" & G1 & "'")*(1st!$A3:$CU500=0)) -- Regards Frank Kabel Frankfurt, Germany "stakar " schrieb im Newsbeitrag ... Hi! I have the following problem In 2 worksheets i have the following In the 1st one i have 99 columns full with values from 0 to 5. On top of each column (A2, B2, C2 ....) there is a text number (01,02,03,...,99). These text numbers using to make the columns to be grouped. So i 've got 10 columns with the header '01', 20 columns with the header '02', 5 columns with the header '03', 14 columns with the header '05' and so on. On the other worksheet i have 5 columns. In each column i count the values of 0 to 5. So in the column A i get the count of 'how many 0 are in the columns of the previous worksheet' in the column B i get the count of 'how many 1 are in the columns of the previous worksheet' in the column E i get the count of 'how many 5 are in the columns of the previous worksheet' What i want is: I use a list under the cell 'G1' on the 2nd worksheet. The list contains the text numbers from 01 to 99 from the 1st worksheet. I want each time a choose a value from the list eg. the '01' all the columns from the 1st worksheet which their header matches to '01' to count the 0 to 5 and the result to be on the 2nd worksheet. The following formula doing what i want but its extremely SLOW and i dont know how to put the value from the list. SUMPRODUCT((1st!$A$2:$CU$2='01')*(1st!$A3:$CU500=0 )) Thanks in advance Stathis --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Look up list of values based on single value from pick list | Excel Discussion (Misc queries) | |||
Identify & List unique values from a list using functions/formulas | Excel Worksheet Functions | |||
create a list of single values from multiple values | Excel Worksheet Functions | |||
Count unique values and create list based on these values | Excel Worksheet Functions | |||
create list of unique values from a column with repeated values? | Excel Worksheet Functions |