countif - determining the range automatically

[b-a-r-t]

Hi,

I want to determine the range in the countif function automatically and relating to a date (i.e. today).

In the attached sheet there are two employees who worked during a certain period, a day worked is 1

Then I would like to count how many days each employee has worked up until today, counting the 1's in the row of that employee until today.

Does anyone have a simple solution to that please ?

Thanks in advance !

Cheers,
Bart
The Netherlands

[Special-K]

This should work

=SUM(INDIRECT(("D"&ROW()&":"&ADDRESS(ROW(),MATCH(B$1,D$3:S$3,0)+3))))

[boopathiraja]

Try

=SUMPRODUCT((D4:S4)*($D$3:$S$3<=$B$1))

[sktneer]

Try this.....

In B4

=SUMPRODUCT(--(ISNUMBER($D$3:$AAA$3)),--($D$3:$AAA$3<=$B$1),--($D4:$AAA4=1))

and copy down.
Where B1=Today().
**Adjust the range as per your need.

Is this what you are trying to achieve?

[johansoder]

Another formula to the buffet table:

Paste in B4:

=COUNTIFS(D3:S3;"<"&B1;D4:S4;1)

Paste in B5:

=COUNTIFS(D3:S3;"<"&B1;D5:S5;1)

[b-a-r-t]

Solved ! Many thanks. I picked the formula of Johansoder as it is the shortest one. Cheers, bart