Count (ifs) values above 0 in a Range

[ILikeCakes]

Hi guys,

I've been working on the for 3 days now and cannot find a solution.

what i need to achieve is the following

I need a formula to count (Paid Weeks) how many cells for each row have a value higher than 0, between the Week Ranges (Week 1_13 etc) in the top row, based on the "Start week" & "Current Week.

for example the first example started in Week 1_13 and has a value in each week until the current Week 8_13, which equates to 8 (Paid Weeks)

in the second example the start week is Week 3_13, and there is a value greater than 0 for 5 weeks up to the current week, which in this instance would equate to 5 (Paid Week)

I'm sure you get the gist by now.
the format below is near on identical to whats used in my workbook.

I have tried countifs, sumproducts, arrays, but i cannot get this to work correctly.

the below countifs is the closest i have come but in areas it counts the Week Ranges instead of the intended row.

D10 represents the Start week of Row
D7 is the current week.

=COUNTIFS(H10:O10,">0",H9:O9,">="&D10,H9:O9,"<="&D7)


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]current week[/TD]
[TD]Week 8_13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name[/TD]
[TD]Start date[/TD]
[TD]start week[/TD]
[TD]Paid
Weeks[/TD]
[TD]Week 1_13[/TD]
[TD]Week 2_13[/TD]
[TD]Week 3_13[/TD]
[TD]Week 4_13[/TD]
[TD]Week 5_13[/TD]
[TD]Week 6_13[/TD]
[TD]Week 7_13[/TD]
[TD]Week 8_13[/TD]
[/TR]
[TR]
[TD]Example[/TD]
[TD][/TD]
[TD]Week 1_13[/TD]
[TD]8[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Example[/TD]
[TD][/TD]
[TD]Week 3_13[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Example[/TD]
[TD][/TD]
[TD]Week 7_13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Example[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks in advance for anyone able to help on this one. I'm sure it's something simple but i just cant get my head around it.

Thanks again

 

[njimack]

This is trickier than it needs to be because of the way you've formatted your week numbers. As a result, the below is a bit messy...

=SUMPRODUCT(--(SUBSTITUTE(SUBSTITUTE(H$9:O$9,"Week ",""),RIGHT($E10,3),)>=SUBSTITUTE(SUBSTITUTE($E10,"Week ",""),RIGHT($E10,3),"")),--(SUBSTITUTE(SUBSTITUTE(H$9:O$9,"Week ",""),RIGHT($D$7,3),)<=SUBSTITUTE(SUBSTITUTE($D$7,"Week ",""),RIGHT($D$7,3),"")),(H10:O10))

 

[NBVC]

I tried your formula and got the results you show... (and 2 for the last example)...

I made some ranges absolute so you can copy down.

=COUNTIFS(H10:O10,">0",$H$9:$O$9,">="&D10,$H$9:$O$9,"<="&$D$7)

This will only work if the weeks are all in the current year. Otherwise it would be better if you used another method of specifying weeks, for example use real dates.

 

[ILikeCakes]

Cheers guys for the responses.

Weeks are formatted in that way as there is a sheet for each week of the year 2013 & 2014 which holds hours across numerous amounts of sites. i use an indirect formula to update the table with the individuals hours so the Week format has to be the same as the sheet its referencing.

I did forget to mention that this table runs for 2013 & 2014 and the formula may need to count between both. i.e if someone has hours in 2013 crossing over to 2014.

so the actual range would need to extended from H to DG for the count ranges.

njimack, i tried the formula and its works great, however when i extend the range it counts 336??
also, could you please advise what you are using column E for??

Again, thanks for the advice

 

[njimack]

When I copied your table into Excel, column E contained the Start Week.

You could keep your existing week numbers, but have the year and Week No in separate rows - this would allow for a much simpler formula.

Assuming your Week Numbers are in row 9...
Year: =2000+RIGHT(H9,2)
Week: =MID(H9,6,FIND("_",H9)-6)+0

 

[ILikeCakes]

Apologies, just checked the formula again and it only counting back from the current week D7 and not taking the start week into consideration.

for example.

week start 2 to current week 8 with values in each cell would equate to 7, but if i change the start week to something like week 6, it should only equate to 3.


thanks

 

[njimack]

Apologies, just checked the formula again and it only counting back from the current week D7 and not taking the start week into consideration.

for example.

week start 2 to current week 8 with values in each cell would equate to 7, but if i change the start week to something like week 6, it should only equate to 3.


thanks

That's exactly what my formula does. It counts from the start week to the current week.

 

[ILikeCakes]

Apologies again, school boy error, miss read the cell reference.

this work perfectly on the given range but when modified to the extended range the calculation goes wild again.
I guess this is all a format issues as when the range is extended to calculation will take the week header into the calculation, where as in the original range you have given it seems to disregard that.

When i extended the range to one year, Week 1_13 to Week 52_13 (H:BG) and insert a value for each cell in the year i get a result of 48 instead of 52, with a black spot of four cells in the columns M:P. funnily enough, this was the same issue i found with my countifs formula. It funny how just those four cells are unreadable. I've checked the entries and they are exactly as the other are.

thanks for the help you have given so far but would you mind adjusting to the desired full range.

D7 will still contain the current week and D10 etc will hold the Start week. the full range of the calculation will be H:DG. Week 1_13 to Week 52_14.

massive thanks again for the help

 

[ILikeCakes]

Hi Again,

okay so what i have done is modify the substitute part of the formula and adjusted the week format to Week 01_13 so that now the substitute reads the cell as 01_13 etc. this works great for the 2013 but when extended to 2014 the calc screws up again.

lost lol


thanks

 

[njimack]

Slight tweak to fix the issue with columns M:P. The below works provided all weeks are in the same year. For a multi-year solution, you'll need to do as I suggested and use separate rows for year and week number (you could still keep your existing headers in a separate row, though).

=SUMPRODUCT(--(SUBSTITUTE(SUBSTITUTE($H$9:$BG$9,"Week ",""),RIGHT($D10,3),)+0>=SUBSTITUTE(SUBSTITUTE($D10,"Week ",""),RIGHT($D10,3),"")+0),--(SUBSTITUTE(SUBSTITUTE($H$9:$BG$9,"Week ",""),RIGHT($D$7,3),)+0<=SUBSTITUTE(SUBSTITUTE($D$7,"Week ",""),RIGHT($D$7,3),"")+0),($H10:$BG10))