stop counting blank cells

[FreakyGirl]

hello

i need a formula that adds the FIRST 6 scores of a player skipping over empty cells with with no data in them.

im using a array formula that works except its counting those empty cells as part of the FIRST 6 so the sum of those 6 is not correct.

any help would be appreciated.

=SUMPRODUCT(N(OFFSET(G3,,MATCH(SMALL(IF(ISNUMBER(I3:U3),COLUMN(I3:U3)),{1,2,3,4,5,6}),IF(ISNUMBER(I3:U3),COLUMN(I3:U3)),0))))

thanks,
Freaky

 

[Debraj]

Try this...

=SUM(I3:INDEX(I3:U3,1,SMALL(IF(ISNUMBER(I3:U3),COLUMN(I3:U3)+1-MIN(COLUMN(I3:U3))),6)))

With Ctrl + shift + Enter

 

[NARAYANK991]

Hi ,

Two things :

1. I do not know why you have asked this question , when it is so similar to the question you have asked earlier ; it was answered to your satisfaction here :

http://chandoo.org/forum/threads/add-7-out-of-14-cells-while-skipping-the-blanks.18291/

2. Secondly , I do not know how your data is laid out , but if you change the G3 to H3 , the formula you have posted works correctly.

=SUMPRODUCT(N(OFFSET(H3,,MATCH(SMALL(IF(ISNUMBER(I3:U3),COLUMN(I3:U3)),{1,2,3,4,5,6}),IF(ISNUMBER(I3:U3),COLUMN(I3:U3)),0))))

The formula has to be entered as an array formula , using CTRL SHIFT ENTER.

Narayan

 

[FreakyGirl]

hello.

i asked the question again because it was not working. the original post was over 3 months ago so i thought it was protocol to make a new post. im sorry if that was wrong & you were offended by it.

approx 2 months ago i had added a column in but i didnt notice that it wasnt working until 2 days ago when the blank cells finally came into play. the sum of the first 6 is only called upon on rare occassions which is why i didnt notice it not working earlier. i dont even see look at that part of the worksheet unless i need to so was easy to overlook.

ty for the help. changing it to H3 did the trick.

im having another issue with a diff formula i rec'd help with on here. do i make a new post or find the original & repost?

thanks
FreakyGirl

 

[NARAYANK991]

Hi ,

I don't think my post in any way suggested I was offended ; I was surprised.

Regarding your other question , you can certainly do either viz. start a new thread or find the original and post your new question there ; it is up to you.

In case you start a new thread , please give full information ; even the person who helped you out the first time , may or may not remember that question or that answer.

Narayan