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/