hi to all,
i have data like this,
<TBODY>
</TBODY><COLGROUP><COL span=5><COL><COL></COLGROUP>
I have data in 15 rows and 7 columns. Now i want to calculate sum for rows contains NE only and then i want to divide this SUM came from sumif formula with total number of NEs and multiplied by 100. Now i am writing sumif formula for 1st set is like =SUMIF($A$2:$E$2,"NE",A4:E4) means i am fixing EQ and NE row and other rows are chaning and then for 2nd set i am using this sumif formula =SUMIF($A$9:$E$9,"NE",A11:E11) here fixing 9th row and other rows are movable till it encounters next set of EQ and NE rows. I have data like this more or less 5000 rows and it will take me lot of time to write sumif formula for eac set. I need help in writing sumif formula to drag it across all the 5000 rows. any help would be appreciated.
Thanks in advance
Genetist.
i have data like this,
AB1</SPAN> | AB2</SPAN> | AB3</SPAN> | AB4</SPAN> | AB5</SPAN> | Sum of NE</SPAN> | %</SPAN> |
EQ</SPAN> | NE</SPAN> | EQ</SPAN> | EQ</SPAN> | EQ</SPAN> | 1</SPAN> | |
0</SPAN> | 0</SPAN> | 1</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> |
0</SPAN> | 1</SPAN> | 0</SPAN> | 0</SPAN> | 1</SPAN> | 1</SPAN> | 100</SPAN> |
0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 1</SPAN> | 0</SPAN> | 0</SPAN> |
1</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> |
1</SPAN> | 1</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 1</SPAN> | 100</SPAN> |
EQ</SPAN> | NE</SPAN> | EQ</SPAN> | EQ</SPAN> | EQ</SPAN> | 1</SPAN> | |
0</SPAN> | 0</SPAN> | 1</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> |
0</SPAN> | 1</SPAN> | 0</SPAN> | 0</SPAN> | 1</SPAN> | 1</SPAN> | 100</SPAN> |
0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 1</SPAN> | 0</SPAN> | 0</SPAN> |
1</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> |
1</SPAN> | 1</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 1</SPAN> | 100</SPAN> |
<TBODY>
</TBODY><COLGROUP><COL span=5><COL><COL></COLGROUP>
I have data in 15 rows and 7 columns. Now i want to calculate sum for rows contains NE only and then i want to divide this SUM came from sumif formula with total number of NEs and multiplied by 100. Now i am writing sumif formula for 1st set is like =SUMIF($A$2:$E$2,"NE",A4:E4) means i am fixing EQ and NE row and other rows are chaning and then for 2nd set i am using this sumif formula =SUMIF($A$9:$E$9,"NE",A11:E11) here fixing 9th row and other rows are movable till it encounters next set of EQ and NE rows. I have data like this more or less 5000 rows and it will take me lot of time to write sumif formula for eac set. I need help in writing sumif formula to drag it across all the 5000 rows. any help would be appreciated.
Thanks in advance
Genetist.