Hi Group

I'm not too sure that my subject is clear, but this is what I want to do.

In a row I have a range of cells, like this

SSS SSS SSS SSS "empty", SSS SSS PPP WWW TTTT "empty" "empty" YY ZZ

Now I want to make a UDF to count the number of the last continous not empty cells in this row. In this case the answer is 2, as there is an empty cell before the last two not empty.

Regards
Jan

hi Jan,

the formula is an array formula to insert with CTRL+Shift+Enter

=COUNTA(INDIRECT(ADDRESS(1,MATCH(FALSE,IF(1:1="",C OLUMN(1:1)),1)+1)&":"&ADDRESS(1,MAX(IF(1:1="",COLU MN(1:1)),1))))

isabelle

Thank you. Didn't think it could be done with a formula.

regards
Jan

Den onsdag den 14. januar 2015 kl. 03.53.49 UTC+1 skrev isabelle:
hi Jan,

the formula is an array formula to insert with CTRL+Shift+Enter

=COUNTA(INDIRECT(ADDRESS(1,MATCH(FALSE,IF(1:1="",C OLUMN(1:1)),1)+1)&":"&ADDRESS(1,MAX(IF(1:1="",COLU MN(1:1)),1))))

isabelle

Sorry but I think I was a Little too fast there.

No matter how many there are, it always comes to the same result, 1. If I leave out the COUNTA from the formula, and only use

=INDIRECT(ADDRESS(1,MATCH(FALSE,IF(1:1="",COLUMN(1 :1)),1)+1)&":"&ADDRESS(1,MAX(IF(1:1="",COLUMN(1:1) ),1)))

I can see that it returns the content of the last cell which has content. And then it apparently only Counts that.

Jan

Den onsdag den 14. januar 2015 kl. 03.53.49 UTC+1 skrev isabelle:
hi Jan,

the formula is an array formula to insert with CTRL+Shift+Enter

=COUNTA(INDIRECT(ADDRESS(1,MATCH(FALSE,IF(1:1="",C OLUMN(1:1)),1)+1)&":"&ADDRESS(1,MAX(IF(1:1="",COLU MN(1:1)),1))))

isabelle

hi Jan,

you have to validate the formula with ctrl + shift + enter each time you edit it

isabelle

Le 2015-01-14 09:08, a écrit :
Sorry but I think I was a Little too fast there.

No matter how many there are, it always comes to the same result, 1. If I leave out the COUNTA from the formula, and only use

=INDIRECT(ADDRESS(1,MATCH(FALSE,IF(1:1="",COLUMN(1 :1)),1)+1)&":"&ADDRESS(1,MAX(IF(1:1="",COLUMN(1:1) ),1)))

I can see that it returns the content of the last cell which has content. And then it apparently only Counts that.

Jan

Den onsdag den 14. januar 2015 kl. 03.53.49 UTC+1 skrev isabelle:
hi Jan,

the formula is an array formula to insert with CTRL+Shift+Enter

=COUNTA(INDIRECT(ADDRESS(1,MATCH(FALSE,IF(1:1="",C OLUMN(1:1)),1)+1)&":"&ADDRESS(1,MAX(IF(1:1="",COLU MN(1:1)),1))))

isabelle

Jan, if you properly validate the formula, you'll see it appear braces { } on
each side of the formula

isabelle

Yes I know, and of course I forgot, but unfortunately that made it worse. Now it Counts 0. But I figured it out right now I think. My row starts in column B, not A, and when I move it to A it Counts right.
So thank you once Again.

Jan

Den onsdag den 14. januar 2015 kl. 15.31.42 UTC+1 skrev isabelle:
Jan, if you properly validate the formula, you'll see it appear braces { } on
each side of the formula

isabelle