Sum X amount of cells in a row based on a seperate cell but ignoring blanks

**How How** · 08-30-2012, 02:12 PM

Hi,
I'm new to this so I hope I explain correctly!!

I have the formula SUM(C4:INDEX(C4:AK4,$AN$3)) where AN3 has the number of cells that are to be added, my problem is I need it to ingore blanks.

Example: C4:AK4 = 18 cells & I need to add the first 5 cells that has values in them but if C7 was blank it would add cells C4,C5,C6,C8 & C9 (ignoring C8).

Can anyone help please?

Thanks

How How

**NBVC** · 08-30-2012, 02:38 PM

This should sum the first 5 non-blanks in C4:AK4

=SUM(C4:INDEX(C4:AK4,SMALL(IF(C4:AK4<>"",COLUMN(C4:AK4)-COLUMN(C4)+1),5)))

confirmed with cTRL+SHIFT+ENTER

**How How** · 08-31-2012, 03:34 AM

Thanks for that NBVC, that worked for some row's & not others, the ones that it worked for is the ones that had 5 or more values, but if the row has <5 values then it didn't work. On my sheet the entered values (By another formula) will vary from 1 to 18. have you a suggestion?

Thanks for your help

How How