Counting number based on cells that have value

[Drayde]

So based on a formula Jonmo gave me, I'm needing to count if there's data in 4 cells... this is a little confusing, so I'll show rather than tell.

=IF(A6=A5,"",INDEX(MMDDYYYY!B:B,(CEILING(ROWS($1:4)/7,1)-1)*49+15))

This, however, currently isn't what I need - it's just returning the value in B15. What I want is for this cell to count whether there's something in these 4 cells, and output a number between 1-4.

So for example, right now.. B15 has John in it, and B16 has Aaron, but B17 and Be18 are empty.

Therefore, it should return a 2, because there's a value in B15 and B16.

[bebo021999]

You should follow this one:
http://www.excelforum.com/excel-form...heet-help.html
to help people to see the whole picture.
BTW, try to post a sample to get quick and better help

[Drayde]

I guess I don't know what you mean/how to follow? I presume that's some linking method, but I'm not familiar enough with the forum to know how that works.

I'll try to create an example to post. Give me a few moments.

[Drayde]

Excel Overnight help.xlsx

Note that I kept all data in the appropriate locations, so there is more data down the MMDDYYYY lines, past what is seen on the first page.

[bebo021999]

How about in D6:

=IF(A6=A5,"",SUMPRODUCT(--ISTEXT(OFFSET(MMDDYYYY!$B$15:$B$18,MAX(INT((ROW(A1)-1)/7)*49),))))

Drag down

[Drayde]

That worked, Bebo! It totally destroyed something else, but I think I can fix that. Appreciate it, sir! For future reference... How do I follow another thread, like you suggested? Or were you just linking for any other readers?

[bebo021999]

Nice to hear it works.
I have seen your first thread with Jonmo's help so I can understand the story, but the other helpers may not.
So you can follow that thread with 2nd raising request, or at least, in this new thread, give a link to first thread by copy/paste the URL.