user krazyderek said:
i have a workbook with sheets named 10, 20, 30 etc... On the Totals sheet i count the occurance of a number on these sheets, each row shows the count of that number on that sheet.
I have a formula that i can drag to populate the matrix,
=if($A2>0,count(iferror(filter(indirect("'"&B$1&"'!$A$2:$A"),indirect("'"&B$1&"'!$A$2:$A")=$A2))),"")
but the real application is quite large, and i'd like to just control it with an array formula so i can update, or delete it without having to drag it out to the rest of the column x row range
i tried =arrayformula(if($A2:$A6>0,count(iferror(filter(indirect("'"&B$1:D$1&"'!$A$2:$A"),indirect("'"&B$1:D$1&"'!$A$2:$A")=$A2:$A6))),""))
but it only continues down the the first column, and the values are all the same as the source cell
here's the link to the spreadsheet sample https://docs.google.com/spreadsheet/ccc?key=0AtSGvdldrQk3dGYtRUZXVzV1dEZsWkFqV1pOZEZySWc
-------------------------------------
following is a solution to the problem
i have a workbook with sheets named 10, 20, 30 etc... On the Totals sheet i count the occurance of a number on these sheets, each row shows the count of that number on that sheet.
I have a formula that i can drag to populate the matrix,
=if($A2>0,count(iferror(filter(indirect("'"&B$1&"'!$A$2:$A"),indirect("'"&B$1&"'!$A$2:$A")=$A2))),"")
but the real application is quite large, and i'd like to just control it with an array formula so i can update, or delete it without having to drag it out to the rest of the column x row range
i tried =arrayformula(if($A2:$A6>0,count(iferror(filter(indirect("'"&B$1:D$1&"'!$A$2:$A"),indirect("'"&B$1:D$1&"'!$A$2:$A")=$A2:$A6))),""))
but it only continues down the the first column, and the values are all the same as the source cell
here's the link to the spreadsheet sample https://docs.google.com/spreadsheet/ccc?key=0AtSGvdldrQk3dGYtRUZXVzV1dEZsWkFqV1pOZEZySWc
-------------------------------------
following is a solution to the problem
No comments:
Post a Comment