I am trying to include an additional function in this formula:
=SUMPRODUCT((Dom_Counts!$B$2:$CY$2<>"")*(Dom_Counts!$B$2:$CY$2=Dom_Counts!B3:CY3))
in order to increment the row numbers by 1 each time I drag it over to the next column without changing the column references.
This formula is referring to another sheet that has an individual's (rows) field of work (columns) marked with a 1 (if they do not work in a field it is left blank) and counts how many fields of work two individuals have in common.
and I need to streamline the process of moving the formula across my sheet (900+ rows/columns) that looks like the one below. I can sacrifice the diagonal formatting and do that part manually later - if that helps.
ex:
<tbody>
</tbody>
I have tried OFFSET a number of different ways, but just can't seem to make it work.
Thank you!
=SUMPRODUCT((Dom_Counts!$B$2:$CY$2<>"")*(Dom_Counts!$B$2:$CY$2=Dom_Counts!B3:CY3))
in order to increment the row numbers by 1 each time I drag it over to the next column without changing the column references.
This formula is referring to another sheet that has an individual's (rows) field of work (columns) marked with a 1 (if they do not work in a field it is left blank) and counts how many fields of work two individuals have in common.
and I need to streamline the process of moving the formula across my sheet (900+ rows/columns) that looks like the one below. I can sacrifice the diagonal formatting and do that part manually later - if that helps.
ex:
| Cell A1 | Carl | Peter | John | Joyce | Ryan |
| Carl | |||||
| Peter | 1 | ||||
| John | 0 | 1 | |||
| Joyce | 0 | 0 | 0 | ||
| Ryan | 2 | 1 | 0 | 0 |
<tbody>
</tbody>
I have tried OFFSET a number of different ways, but just can't seem to make it work.
Thank you!
Last edited:
