taking a count

[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

 

[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

 

[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:

 

[Nikhil]

Yo!!

You Rock!!!

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