Solved: again problem with counting date in week

[Ger]

Hi,

again i have a [problem with counting the dates to a week. The problem is that the formula =SOMPRODUCT(--(1+INTEGER(($E$5:$M$29-(DATUM(JAAR($E$5:$M$29);1;2)-WEEKDAG(DATUM(JAAR($E$5:$M$29);1;1))))/7)=C32)) doesn't accept a reference to an other worksheet. So i give it a value (0). But now i have the problem that i cann't count the value's in the row because 0 is also a value.

See the example (column O)

Ger
:banghead:

[Bob Phillips]

That formula can reference another sheet okay. Which count are you referring to?

[Ger]

In the example i had to change the value "" to 0 because i get the value "waarde" with the formula =SOMPRODUCT(--(1+INTEGER(($E$5:$M$29-(DATUM(JAAR($E$5:$M$29);1;2)-WEEKDAG(DATUM(JAAR($E$5:$M$29);1;1))))/7)=C32)) . Now i have problems with counting the rows because there is a 0 in each cell

[Bob Phillips]

I am sorry, I am just not following what the problem is. Where did you have to change "" to 0, where is the problem occurring, and what happedn to the last change to accomodate the spurious Jan dates?

[Ger]

In the attached file you can see the problem. The first is with zero if there is no value in the cell the second (row 37 to 70) is with "" if there is no value in the linked cells. So i hope you can see my problem now.


Ger

[Bob Phillips]

Use this in O6 etc.

=AANTAL.ALS(E6:N6;">0")

[Ger]

Thanks again.

this works fine.


Ger