Excel Formula Help

[wjmcgreevy]

I apologize in advance, Im not sure that I really know how to explain this.

I have a formula that I want to add various cells. For Instance, a5, a10, a15, a20 and so on. My question is that data is added bi weekly and always 5 cells down. So the next cell would be a25. Instead of writing a forumlay =sum(a5,...a25) adn then having to add the next number in each time, is there a way to make a formula that will just take into account every 5th cell in column A? That way when they put in data it will automatically update the rest of the spreadsheet instead of having to add to the formula first.

 

[Zathras]

You could use an array formula like this:
[tt]
=SUMPRODUCT(A1:A6000,IF(MOD(ROW(A1:A6000),5),0,1))
[/tt]
An array formula is entered by using Ctrl+Shift+Enter after typing/pasting the formula in the input window. When done properly, it appears bracketed by curly braces.

 

[wjmcgreevy]

Either this is not working, or I'm not putting it in right, most likley the latter. I tried it in a test and it gave a num error. Here are the specifics of my spreadsheet.

My first value is located in T5, the next in T19, T33, then every 14 after that. I would jus use a T:T type formula but there are other values in the T row that I need excluded. T6 through T12 have values in them that I want to add the same way as the value in T5.

 

[LaurieOZ]

As your data starts at row 5 (T5)then Zathras formula would need to be modified to adj the MOD-ROW reference as below
Also note the change to sum the 14 instead of 5th row

=SUMPRODUCT(T5:T6000,IF(MOD(ROW(T5:T6000)-4,14),0,1))



Remember Ctrl+Shift+Enter

Laurie

 

[Rofeu]

Hi,

Zathras was on the right path, but to my knowledge, SUMPRODUCT doesn't work with an IF in it.

Use the following as an array formula instead:

=SUMPRODUCT(A1:A10*(MOD(ROW(A1:A10),5)=0))

Cheers,

Roel

 

[LaurieOZ]

Hi Roel

Zathras's formula will work, however over a large spreadsheet the array formula will be less efficient than yours.

As wjmcgreevy data started at T5 (the 5th row)the Mod function needs to be adjusted as mentioned in earlier post and the "5" changed to 14 representing the "Nth" row to sum.

ie (MOD(ROW(T5:T10)-4,14)=0

Regards

Laurie

 

[wjmcgreevy]

Thanks for the help guys. Laurie02 before your post I got this to work with the following

=SUMPRODUCT(('2007'!U1:U10000="Score")*('2007'!T1:T10000))

I couldnt get their sumproduct one to work for some reason. This seems to be working 100% so far. I am really not very advanced with excel. Is this formula OK? or do you see it causing problems in the future?

 

[yogia]

Hi wjmcgreevey:

How about the following ...

=SUMPRODUCT(A1:A50,--(MOD(ROW(A1:A50),5)=0))

It works for me.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[LaurieOZ]

Hi wjmcgreevey:

You have introduced column "U" and a "score" category.

Without knowing how they were constructed we can't tell.

In each of Zathras,Rofeo, and Yogi's formulas
the assumption is your data starts in A1 and you want every 5th number to sum
The "ROW-Mod" formula is directly related to A1 reference.
If data starts in A2 formula becomes (MOD(ROW(A2:A10)-1,5)=0
If data starts in A3 formula becomes (MOD(ROW(A3:A10)-2,5)=0
etc.
Hope this helps

Laurie