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)

current week	Week 8_13
name	Start date	start week	Paid Weeks	Week 1_13	Week 2_13	Week 3_13	Week 4_13	Week 5_13	Week 6_13	Week 7_13	Week 8_13
Example		Week 1_13	8	1	1	1	1	1	1	1	1
Example		Week 3_13	5			1	1	1	0	1	1
Example		Week 7_13								1	1
Example

<tbody>
</tbody>

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

 

[ILikeCakes]

Thanks this is brilliant.

I have added in the rows suggested for year and week. what sort of formula would we being looking at using for this??


Thanks

 

[njimack]

Assuming your years are in row 7 and week numbers in row 8, start date is in D10, current week is in D7, weekly data starts from column H. Enter the following array formula (confirm with ctrl+shift+enter):

=SUM(OFFSET($G10,0,MATCH(RIGHT($D10,2)+2000&MID($D10,6,FIND("_",$D10)-6),$H$7:$DG$7&$H$8:$DG$8,0),,MATCH(RIGHT($D$7,2)+2000&MID($D$7,6,FIND("_",$D$7)-6),$H$7:$DG$7&$H$8:$DG$8,0)-MATCH(RIGHT($D10,2)+2000&MID($D10,6,FIND("_",$D10)-6),$H$7:$DG$7&$H$8:$DG$8,0)))

 

[ILikeCakes]

Hi

Tried this but as you can see from the results below, the calculations are incorrect. I have tried to amend this but failed.
Thanks again for all your help. Greatly appreciated

Week 14_14			Year	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2013	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014	2014
			Week	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35	36	37	38	39	40	41	42	43	44	45	46	47	48	49	50	51	52	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35	36	37	38	39	40	41	42	43	44	45	46	47	48	49	50	51	52
Weeknum	LOS	Paid Weeks	AWR?	Week 01_13	Week 02_13	Week 03_13	Week 04_13	Week 05_13	Week 06_13	Week 07_13	Week 08_13	Week 09_13	Week 10_13	Week 11_13	Week 12_13	Week 13_13	Week 14_13	Week 15_13	Week 16_13	Week 17_13	Week 18_13	Week 19_13	Week 20_13	Week 21_13	Week 22_13	Week 23_13	Week 24_13	Week 25_13	Week 26_13	Week 27_13	Week 28_13	Week 29_13	Week 30_13	Week 31_13	Week 32_13	Week 33_13	Week 34_13	Week 35_13	Week 36_13	Week 37_13	Week 38_13	Week 39_13	Week 40_13	Week 41_13	Week 42_13	Week 43_13	Week 44_13	Week 45_13	Week 46_13	Week 47_13	Week 48_13	Week 49_13	Week 50_13	Week 51_13	Week 52_13	Week 01_14	Week 02_14	Week 03_14	Week 04_14	Week 05_14	Week 06_14	Week 07_14	Week 08_14	Week 09_14	Week 10_14	Week 11_14	Week 12_14	Week 13_14	Week 14_14	Week 15_14	Week 16_14	Week 17_14	Week 18_14	Week 19_14	Week 20_14	Week 21_14	Week 22_14	Week 23_14	Week 24_14	Week 25_14	Week 26_14	Week 27_14	Week 28_14	Week 29_14	Week 30_14	Week 31_14	Week 32_14	Week 33_14	Week 34_14	Week 35_14	Week 36_14	Week 37_14	Week 38_14	Week 39_14	Week 40_14	Week 41_14	Week 42_14	Week 43_14	Week 44_14	Week 45_14	Week 46_14	Week 47_14	Week 48_14	Week 49_14	Week 50_14	Week 51_14	Week 52_14
Week 44_13		#N/A																																													15	20	10	20	20	20	20	20	20	20	20	20	15	20	20	20	20	20	10	20	20	20
Week 37_13		1216																																					0	40	40	40	40	40	40	40	40	40	50	51	62	55	53	54	21	30	40	40	34	40	41	45	40	40	40	40	40	40																																							1
Week 45_13		840																																														45	39	40	53	59	51	53	30	30	40	40	40	40	40	30	30	40	40	40	40	20
Week 45_13		820																																														45	40	40	40	44	51	60	30	30	40	40	40	0	40	50	45	40	45	40	40	20
Week 44_13		944																																													45	40	50	45	50	55	56	50	30	30	50	22	38	40	50	42	25	40	50	47	50	40
Week 37_13		1040		12	14	27	32	0	0	0	15	0																												40	40	32	40	40	40	40	40	40	40	40	51	55	51	50	30	30	25	40	28	33	20	20	20	40	40	25	30	20
Week 32_13		1305																																	32	39	40	44	46	50	50	34	50	40	40	50	40	0	50	40	50	55	53	60	10	20	0	20	40	41	41	40	20	40	40	30	40	60
Week 43_13		972																																											0	40	43	40	50	40	61	60	53	60	10	20	40	40	40	41	41	39	45	40	40	40	40	50
Week 47_13		730																																																5	50	55	53	50	11	20	40	40	40	41	40	40	40	40	40	40	40	45
Week 43_13		881																																												45	34	40	40	45	50	41	53	53	30	30	40	40	25	30	40	46	45	37	2	37	40	40
Week 40_13		1006																																									30	50	40	40	30	45	40	50	51	60	46	60	30	30	40	40	20	0	20	40	40	40	45	40	40	40
Week 40_13		914																																									40	30	40	40	40	40	30	24	50	44	57	45	0	20	20	40	35	30	0	40	45	40	50	35	40	40
Week 32_13		1285																																	40	40	40	44	40	48	32	40	40	40	40	40	40	45	40	45	30	60	52	52	30	30	30	40	40	20	41	46	40	20	0	20	40	40
Week 45_13		815																																														25	50	40	50	59	52	50	30	30	39	40	40	40	40	40	30	40	40	0	40	40
Week 45_13		802																																														25	50	40	50	55	52	51	30	30	30	35	40	40	40	40	30	45	40	0	40	40
Week 46_13		741																																															5	40	50	56	54	50	28	29	40	40	40	30	40	40	40	40	40	0	40	40
Week 41_13		937																																									0	40	20	40	40	40	40	40	50	54	54	50	20	29	40	41	40	40	40	40	20	0	40	40	40	40
Week 42_13		973																																											5	40	40	40	40	40	50	55	54	53	28	29	41	40	37	40	42	44	35	40	41	40	40	60
Week 30_13		1485																															27	28	40	40	40	37	40	40	40	32	40	40	45	40	35	45	48	48	50	55	54	50	28	27	50	40	45	30	45	48	48	40	46	48	38	40
Week 47_13		733																																																5	50	56	54	55	15	39	40	40	40	40	40	40	25	35	40	40	40	40
Week 47_13		751																																																5	51	60	54	42	28	39	41	40	41	41	41	40	38	25	41	36	40	51
Week 47_13		731																																																5	50	55	54	40	28	39	40	40	40	40	40	40	40	40	40	40	40	20
Week 24_13		1355																									16	30	8	32	32	32	32	32	32	24	32	26	32	32	32	32	40	33	30	40	40	25	40	35	40	33	52	30	13	39	30	40	20	40	20	20	40	40	40	40	40	40
Week 43_13		910																																												5	40	40	40	45	50	55	54	50	38	39	30	30	32	40	40	46	37	40	40	40	40	40
Week 32_13		1354																																	40	40	40	41	48	40	46	40	40	36	40	35	11	40	40	46	55	51	50	60	16	24	40	40	34	35	24	40	40	36	50	50	40	47
Week 37_13		1237																																						36	36	42	50	40	40	40	40	40	40	46	55	60	50	58	30	30	40	40	40	40	40	40	40	40	46	40	48	50
Week 30_13		1317																															16	40	24	24	36	32	42	48	44	48	30	31	40	50	50	30	50	50	55	55	55	47	28	23	35	18	40	34	10	40	40	25	35	40	20	34
Week 44_13		738																																													5	40	40	40	53	55	40	40	27	25	31	40	34	30	35	37	40	40	0	0	40	48
Week 47_13		731																																																5	50	55	54	40	28	39	40	40	40	40	40	40	40	40	40	40	40	20
Week 48_13		653																																																	26	55	52	51	40	40	40	20	40	30	41	36	40	40	20	30	32	20

<colgroup><col span="3"><col><col span="9"><col span="43"><col span="9"><col span="43"></colgroup><tbody>
</tbody>

 

[njimack]

I can't upload anything as I can't install Excel Jeanie on my work pc. When I get home this evening I'll upload the solution for you.

 

[ILikeCakes]

Cheers, you have been a great help


thanks

 

[ILikeCakes]

the formula is suming the values not counting. other than that it does exactly what it says on the tin

 

[njimack]

the formula is suming the values not counting. other than that it does exactly what it says on the tin

In the example data you provided, the values were only 1 or 0, hence no difference between summing or counting! You can easily change it to a COUNT function if you want.