add 7 out of 14 cells while skipping the blanks

[FreakyGirl]

hello

i have a range of 14 cells E2:R2 that have to be added. The cells will only contain a value of 0-8 or be blank.

I only want to know the SUM of the 1st 7 cells that have a number in them skipping over the blank ones. I know how to add them all using SUM but not just 7 values.

I cant choose ahead of time which cells will be blank or will have a value. If there are less than 7 scores thats ok but once 7 cells have a value i need the sum of those 7 cells only. doesnt matter if they are the highest 7, lowest 7 - just the first 7 skipping over the blanks.

My knowledge is limited so but I dont know if i should be using a CountIf cause of the blanks or a SumIf??

Please help!!

FreakyGirl

 

[Hui]

FreakyGirl

This monster will do your job
=SUMPRODUCT(N(OFFSET(D2,,MATCH(SMALL(IF(ISNUMBER(E2:R2),COLUMN(E2:R2)),{1,2,3,4,5,6,7}),IF(ISNUMBER(E2:R2),COLUMN(E2:R2)),0)))) Ctrl+Shift+Enter

 

[Hui]

If you want to make the Number 7 variable
use this variation:
=SUMPRODUCT(N(OFFSET(D2,,MATCH(SMALL(IF(ISNUMBER(E2:R2),COLUMN(E2:R2)),ROW(OFFSET($A$1,,,7))),IF(ISNUMBER(E2:R2),COLUMN(E2:R2)),0))))

 

[Ananda Kumar C]

Hi,

The formula would return an error if there are less than 7 values.

In addition, the result of the MATCH function, I guess, would be to simply reduce each of the output values of SMALL function by 4 (column of D2), which can be also achieved by a formula.

Also I think SUM itself would do the job.

I have used this formula taking into account the above.

=SUM(N(OFFSET(D2,,IFERROR(SMALL(IF(ISNUMBER(E2:R2),COLUMN(E2:R2)),{1,2,3,4,5,6,7})-COLUMN(D2),FALSE))))

Thanks,
Anand

 

[Hui]

You could use this modified version
=SUMPRODUCT(N(OFFSET(D2,,MATCH(SMALL(IF(ISNUMBER(E2:R2),COLUMN(E2:R2)),ROW(OFFSET($A$1,,,MIN(COUNT(E2:R2),7)))),IF(ISNUMBER(E2:R2),COLUMN(E2:R2)),0)))) Ctrl+Shift+Enter

This will use the lower of the number of items in E2:R2 or 7, whichever is lowest

 

[FreakyGirl]

LOL once again, i would have never gotton close to those formulas. However they help me to learn because i look up names i dont know like OFFSET to help me understand the forumlas. Ty for both for your input. I dont mind the #NUM error as its in my helper column & expected.

When you say 'make the 7 a variable' do you mean i change it to a 6 for example & it will only add the 1st 6 boxes with values in them? in other words, whatever number that 7 represents will changes how many are added?

thanks again for sharing

FreakyGirl

 

[Hui]

That's correct
In my formula you can change the red 7 to say A1
Then put the number of values you wish to sum in cell A1 eg: 7

Pulling apart formulas is the best way to learn the powers of Excel