count numbers on or before today

[Carolief]

Hi,

I've got an excel sheet in which the amount of hours someone worked on a day are administrated (column c) by date (column b in 'dd mmm' format). When someone didn't work, the cell has to be empty.
I want to count the days that someone worked, including today.
<table border="0" cellpadding="0" cellspacing="0" width="128"><col style="width: 48pt;" width="64"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 12pt;" height="16"> <td class="xl75" style="height: 12pt; width: 48pt;" align="right" width="64" height="16">B
11 Nov</td> <td class="xl76" style="width: 48pt;" align="right" width="64">C
9.0</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl75" style="height: 12pt;" align="right" height="16">12 Nov</td> <td class="xl76" align="right">10.5</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl75" style="height: 12pt;" align="right" height="16">13 Nov</td> <td class="xl76">
</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl75" style="height: 12pt;" align="right" height="16">14 Nov</td> <td class="xl76">
</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl75" style="height: 12pt;" align="right" height="16">15 Nov</td> <td class="xl76" align="right">9.0</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl75" style="height: 12pt;" align="right" height="16">16 Nov</td> <td class="xl76" align="right">8.5</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl75" style="height: 12pt;" align="right" height="16">17 Nov</td> <td class="xl76" align="right">8.5</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl75" style="height: 12pt;" align="right" height="16">18 Nov</td> <td class="xl76" align="right">
</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl75" style="height: 12pt;" align="right" height="16">19 Nov</td> <td class="xl76" align="right">3.5</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl75" style="height: 12pt;" align="right" height="16">20 Nov</td> <td class="xl76">
</td> </tr> </tbody></table>
For the example above the formula should give 4 (have worked on 11, 12, 15 and today).
Hope someone can help me! Has been taking me way too long again...

Carolien

 

[PaddyD]

assuming your data is in c1:d11 (with column headers):

=SUM(IF(C2:C11<=TODAY(),IF(LEN(D2:D11)>0,1/COUNTIF(C2:C11,C2:C11))))

...entered with control + shift + enter, not just enter. if you want to understand what's going on in the core of the formula:

http://www.mrexcel.com/forum/showthread.php?t=70835

 

[Carolief]

Thanks! That works! Took me a while to understand though .
Would you know how to solve it if there are days with 0 entered for worked hours which shouldn't be counted at worked days? Don't worry about answering if it takes too much time or effort, it's not important! I'm just curious.
Thanks again, for your quick reply and your great answer!

Carolien

 

[Aladin Akyurek]

Thanks! That works! Took me a while to understand though .
Would you know how to solve it if there are days with 0 entered for worked hours which shouldn't be counted at worked days? Don't worry about answering if it takes too much time or effort, it's not important! I'm just curious.
Thanks again, for your quick reply and your great answer!

Carolien

Are there repeats in the dates range, that is, 11 nov occurs more than once, for example? If 11 nov occurs twice and is associated with 9.0 and 10.0, does it count as 1 or 2?

 

[Carolief]

There are no repeats in the dates range. Thanks!

 

[Aladin Akyurek]

There are no repeats in the dates range. Thanks!

Assuming that the range is B2:C11...

Either:

Control+shift+enter, not just enter...

=SUM(IF(C2:C11>0,IF(B2:B11<=TODAY(),1)))

Or:

Just enter...

=SUMPRODUCT(--(C2:C11>0),--(B2:B11<=TODAY()))

 

[Carolief]

Thanks! I was so close... Nice to have other people who want to help you out on such a moment. That's great!

Carolien