# Unknown Error in Array Formula Using Sum()

#### deep_shobhit

##### New Member
Hi,

I'm getting an error while using the formula given below in Excel. The version I'm using is Excel 2002 SP3.

Code:
``=SUM(IF(MOD(1,(COLUMN(INDIRECT(ADDRESS(ROW(),2) & ":" & ADDRESS(ROW(), COLUMN()-1))))- (COLUMN(INDIRECT(ADDRESS(ROW(),2))))+ 1- (ABS(INT((COLUMN(INDIRECT(ADDRESS(ROW(),2) & ":" & ADDRESS(ROW(), COLUMN()-1)))-COLUMN(INDIRECT(ADDRESS(ROW(),2)))+1)/3-0.1))*3))=0,INDIRECT(ADDRESS(ROW(),2) & ":" & ADDRESS(ROW(), COLUMN()-1)),0))``

After giving the error, the first ROW() after teh ABS function is highlighted by Excel. However; If I'm selecting the formula from IF condition i.e. parameter to the SUM() and pressing F9 to evaluate, the values are coming fine. When I'm using SUM(), only then it is giving me the problem.

What I'm trying to do exactly from this formula is:

I have some values in the row in the set of 3, i.e. Questions Given, Attempted and Correct. I want to display the total of these three values in the last 3 cells.
So its like

---------------Question 1---Question 2---Question 3------Total
Student 1---1---1---0------1---1---0------1---1---0------3---3---0
Student 2---1---0---0------0---0---0------1---1---1------2---1---1
Student 3---1---1---1------1---0---0------1---1---1------3---2---2

The figures in the blue above are what I'm trying to derive. So, I want this formula to be of a generic kind of formula as the 3 Set Columns in the above data (for questions) are variable.

So, if you want to replicate the same problem on your system, enter following data in different columns in a worksheet starting from first column in any row:

0 -- 1 -- 2 -- 3 -- 1 -- 2 -- 3 -- 1 -- 2 -- 3 and the formula in the next cell.

Ideally, this formula should give the results as 3 (i.e. sum of figures in the positions having 1s or we can say every third position)

Any help for this will be highly appreacited. As I'm new to array formulas, I have already spent around 6 hours on this thing.

Regards
Shobhit

I have added -s in the codes/example to give worksheet like structure to the data. Please consider the -s as delimiter.

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### Andrew Poulsom

##### MrExcel MVP
I think you've hit the maximum of 7 nested levels of functions.

What's wrong with?

=SUM(B2,E2,H2)

in K2 copied down and across?

#### deep_shobhit

##### New Member
Looks like that only....

Thanks Andrew,

Actually, I'm generating the XML for the report from my ASP.NET pages, and rendering that report data to Excel through XSLT. Now the number of questions in my case are not fixed. These can be anything.

Therefore I was trying to derive a generic function which can work in all conditions.

Looks like the same thing as you have suggested, max 7 level of functions...

Can I club some thing together so that this thing can work?

Regards
Shobhit

#### Andrew Poulsom

##### MrExcel MVP
This does the job:

=SUMPRODUCT(--(MOD(COLUMN(\$B\$1:\$J\$1),3)=MOD(COLUMN(),3)),\$B2:\$J2)

But there is a constant 3 in the formula so it's not truly generic.

#### fairwinds

##### MrExcel MVP

Hi,

This is an inefficiant formula but I believe it replicates more what you were after above:

=SUMPRODUCT(--(MOD(COLUMN(INDEX(INDIRECT("A:IV"),ROW(),2):INDEX(INDIRECT("A:IV"),ROW(),COLUMN()-1))-2,3)=0),INDEX(INDIRECT("A:IV"),ROW(),2):INDEX(INDIRECT("A:IV"),ROW(),COLUMN()-1))

#### deep_shobhit

##### New Member
Thanks Andrew, Fairwinds,

The problem with the formula I have posted was exactly the same thing like suggested by Andrew, 7 nested function limit. However; I figured out that the ABS() was not required at all. I removed that and it worked. So eventually I have three formulas now which works for three digits in the above case.

For first summary calculated field:
Code:
``=SUM(IF(MOD(1,(COLUMN(INDIRECT(ADDRESS(ROW(),2) & ":" & ADDRESS(ROW(), COLUMN()-1))))- (COLUMN(INDIRECT(ADDRESS(ROW(),2))))+ 1- (INT((COLUMN(INDIRECT(ADDRESS(ROW(),2) & ":" & ADDRESS(ROW(), COLUMN()-1)))-COLUMN(INDIRECT(ADDRESS(ROW(),2)))+1)/3-0.1))*3)=0,INDIRECT(ADDRESS(ROW(),2) & ":" & ADDRESS(ROW(), COLUMN()-1)),0))``
For second Summary calculated field:
Code:
``=SUM(IF(MOD(3,(COLUMN(INDIRECT(ADDRESS(ROW(),2) & ":" & ADDRESS(ROW(), COLUMN()-2))))- (COLUMN(INDIRECT(ADDRESS(ROW(),2))))+ 1- (INT((COLUMN(INDIRECT(ADDRESS(ROW(),2) & ":" & ADDRESS(ROW(), COLUMN()-2)))-COLUMN(INDIRECT(ADDRESS(ROW(),2)))+1)/3-0.1))*3)=1,INDIRECT(ADDRESS(ROW(),2) & ":" & ADDRESS(ROW(), COLUMN()-2)),0))``
and finally for third Summary calculated field:
Code:
``=SUM(IF(MOD(4,(COLUMN(INDIRECT(ADDRESS(ROW(),2) & ":" & ADDRESS(ROW(), COLUMN()-3))))- (COLUMN(INDIRECT(ADDRESS(ROW(),2))))+ 1- (INT((COLUMN(INDIRECT(ADDRESS(ROW(),2) & ":" & ADDRESS(ROW(), COLUMN()-3)))-COLUMN(INDIRECT(ADDRESS(ROW(),2)))+1)/3-0.1))*3)=1,INDIRECT(ADDRESS(ROW(),2) & ":" & ADDRESS(ROW(), COLUMN()-3)),0))``

The formulas for three different fields are different but the only changes are the index numbers and the first parameter of the MOD().

I also tried using SUMPRODUCT, but got a little confused. This function gives the product of two arrays, whereas I wanted the sum total of the ranges. Will look more into this function as I hope this might be more easier to accomplish the same task if it can be used in this case.

Actually, It would have been much easier if I would have used custom function but again, in that case I can't use the code in XML Based Excel Sheet. It does not allows you to add custom code while saving the excel sheet as xml workbook.

Thanks a ton for the help. Please let me know if I can make it better.

Regards
Shobhit

#### fairwinds

##### MrExcel MVP
I also tried using SUMPRODUCT, but got a little confused. This function gives the product of two arrays, whereas I wanted the sum total of the ranges.

SUMPRODUCT does as you describe. However in this case, whether you use Andrew's or my formula, it will multiply one array with result of the conditions. E.g. {1,0,0,1,0,0,1,0,0} with the array holding your numbers. and then sum. Thus you will get the same result as your formula.

SUMPRODUCT has the advantage that you do not need to Ctrl + Shift + Enter.

#### pgc01

##### MrExcel MVP
Hi deep_shobhit

This formula seems to be equivalent to any of your 3 formulas:

Code:
``=SUM(N(INDIRECT(ADDRESS(ROW(),-1+MOD(COLUMN()+1,3)+3*ROW(INDIRECT("1:"&INT((COLUMN()-2)/3)))))))``
Confirmed with CTRL+SHIFT+ENTER

If you try it, please post back the result.

Hope this helps
PGC

Replies
5
Views
181
Replies
6
Views
111
Replies
7
Views
445
Replies
2
Views
290
Replies
2
Views
251

### Forum statistics

1,141,679
Messages
5,707,787
Members
421,527
Latest member
Tamiwsw ### 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.

### Which adblocker are you using?    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

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