Wednesday, May 19, 2010

Sum Every nth Data in a Range

My Struggle and at the same time great fun with Excel continues... Must say, there's something new that I learn almost everyday....

Hurray ! I got a solution to this one and this time was quick too... Bt ain't sure if this is the most efficient solution because I had to insert an additional column to get the desired result.

Scenario:
From the given range of data, user needs to sum only nth data. Say, sum every 3rd data or every 5th data, etc
Refer to the below snapshot:



From the Snapshot:
1. Sample Data
2. Cell where criteria to sum every nth data is given
3. Additional inserted column
4. Refers to cell where result is displayed

Hereby is the list of steps:
1. In the inserted additional column A, use the below formula in Cell A2:
=IF(MOD(ROW(B2)-1,$E$1)=0,1,0)



2. Copy the formula to the cell which has data in adjacent column B

Remarks: You will get 1 in every cell which matches the criteria given in cell E1.

3. For Result in Cell E2, used the formula: =SUMIF(A2:B11,1,B2:B11)



4. Verify the result by changing the data in Cell E1.

Mission Accomplished !!

No comments:

Post a Comment