how to write dynamic SUMIF formula across all the rows in a coulmn

genetist

Board Regular
Joined
Mar 29, 2013
Messages
75
Office Version
  1. 2016
Platform
  1. Windows
hi to all,
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.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
In your sample data the entries in A2:E2 are the same as in A9:E9, so why not just use $A$2:$E$2?

Dear Andrew,

Thank you very much for your reply, here in my sample data the entries are same but this is not the case for remaining rows there may be diferrences. i want to use one SUMIF formula even though there is differences in EQ and NE rows.
Thanks once again.

Regards,
Genetist
 
Upvote 0
Another way:

Code:
G4-> =IF(OR(A4="",ISTEXT(A4)),"",
SUMIF(OFFSET($A$2:$E$2,CEILING(ROWS($G$4:G4),7)-7,),"NE",A4:E4)*100)

Or

G4-> =IF(MOD(ROWS($G$4:G4)-1,7)<5,
SUMIF(OFFSET($A$2:$E$2,CEILING(ROWS($G$4:G4),7)-7,),"NE",A4:E4)*100,"")

Markmzz
 
Last edited:
Upvote 0
A small modification in my formula:

Code:
F4-> =IF(ISNUMBER(A4),
SUMIF(OFFSET($A$2:$E$2,MATCH("ZZ",$A$2:A2)-1,),"NE",A4:E4),"")

Markmzz
 
Upvote 0
Dear Andrew and Markmzz,
Thank you very much for your replies to my querie.
Andrew i tried your formula in F4 and i am getting all zero even though i have 1 in NE column.

Markmazz i tried your formula in F4 and i am getting all Zeros even though i have 1 under NE column and i tried your another formula in G4 and it is fine but if my sum of NE column contains 2 my % value is going to 200%. My % should get maximum 100% only.
once again thank you very much.
 
Upvote 0
Dear Andrew and Markmzz,
Thank you very much for your replies to my querie.
Andrew i tried your formula in F4 and i am getting all zero even though i have 1 in NE column.

Markmazz i tried your formula in F4 and i am getting all Zeros even though i have 1 under NE column and i tried your another formula in G4 and it is fine but if my sum of NE column contains 2 my % value is going to 200%. My % should get maximum 100% only.
once again thank you very much.

Genetist,

Look at your PM box.

Markmzz
 
Upvote 0
Genetist,

Here are the last formulas:

Layout

AB1
AB2
AB3
AB4
AB5
Sum of NE
%
Sum of NE
%
EQ
NE
NE
EQ
NE
3
3
*
0
0
1
0
0
1
33
1
33
0
1
0
0
1
2
67
2
67
0
0
0
0
1
1
33
1
33
1
0
0
0
0
0
0
0
0
1
1
0
0
0
1
33
1
33
EQ
NE
EQ
NE
EQ
2
2
*
0
0
1
0
0
0
0
0
0
0
1
0
0
1
1
50
1
50
0
0
0
0
1
0
0
0
0
1
0
0
0
0
0
0
0
0
1
1
0
0
0
1
50
1
50
EQ
EQ
EQ
NE
NE
2
2
*
0
0
1
0
0
0
0
0
0
0
1
0
1
1
2
100
2
100
0
0
0
0
1
1
50
1
50
1
0
0
1
0
1
50
1
50
1
1
0
0
1
1
50
1
50
*****
*****
*****
*****
*****
*********
*********
*********
*********

<tbody>
</tbody>


Formulas

Code:
F2-> =IF(ISTEXT(E2),COUNTIF(A2:E2,"NE"),IF(ISNUMBER(E2),SUMIF(OFFSET($A$2:$E$2,CEILING(ROWS($F$2:F2),7)-7,),"NE",A2:E2),""))

G2-> =IF(ISNUMBER(A2),100*F2/OFFSET($F$2,CEILING(ROWS($G$2:G2),7)-7,),"")

H2-> =IF(ISTEXT(E2),COUNTIF(A2:E2,"NE"),IF(ISNUMBER(A2),SUMIF(OFFSET($A$2:$E$2,MATCH("ZZ",$A$2:A2)-1,),"NE",A2:E2),""))

I2-> =IF(ISNUMBER(A2),100*H2/OFFSET($H$2,MATCH("ZZ",$A$2:A2)-1,),"")

Markmzz
 
Upvote 0
Andrew i tried your formula in F4 and i am getting all zero even though i have 1 in NE column.

My formula works for me:


Excel 2010
ABCDEF
1AB1AB2AB3AB4AB5
2EQNEEQEQEQ
3
4001000
5010011
6000010
7100000
8110001
9NENEEQEQEQ0
100
11001000
12010011
13000010
14100001
15110002
Sheet2
Cell Formulas
RangeFormula
F4=SUMIF(INDEX(A:E,LOOKUP(2,1/ISTEXT(A$1:A3),ROW(A$1:A3)),0),"NE",A4:E4)
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top