taking a count

N

Nikhil

I have dates in Col A, text in Col B & Col C.

In Col D will have a count of Col B & Col C ( a range of cells from Col B &
Col C). However i want this count in only that row where the date in Col A
corresponds to Sunday.

Also the count has to be for the entire week i.e. from Monday to Saturday.

e.g. if the date in A1 corresponds to Monday, (A7 will thus be Sunday), then
D7 should give me a count of B1:C7. Similarly D14 should give a count of B8:
C14.

However please note that A1 will not necessarily start from Monday or the
1st of the month. It can be any weekday.

Plz help

Nikhil
 
J

Jacob Skaria

Try this formula in D7 and copy down as required... This formula will count
the range of cells in ColB and ColC from previous monday to Saturday..

=IF(AND(ISNUMBER(A7),WEEKDAY(A7)=1),COUNT(B1:C7),"")

If this post helps click Yes
 
S

Stefi

Try this
=IF(WEEKDAY(A1,2)=7,IF(ROW()=1,0,COUNTA(OFFSET(INDIRECT(ADDRESS(MAX(ROW()-6,1),2,4,1)),0,0,MIN(6,ROW())-1,2))),"")

Regards,
Stefi

„Nikhil†ezt írta:
 
N

Nikhil

Yo!!

You Rock!!!

Jacob Skaria said:
Try this formula in D7 and copy down as required... This formula will count
the range of cells in ColB and ColC from previous monday to Saturday..

=IF(AND(ISNUMBER(A7),WEEKDAY(A7)=1),COUNT(B1:C7),"")

If this post helps click Yes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top