=COUNTIF(C2:L2,0) question - start at first value

[timfear]

I'm not even sure how to ask this. I am creating a calculator for a loyalty points program. According to the program rules, a company gets a certain amount of points for every year it participates in the program, but loses two points each time it does not.

I used the formula above within another formula and it works great. Every time a zero shows up in a company's row, that company gets dinged with -2 points. All good.

However, the system goes back ten years and the idea is that if theacompany participated for eight years, then skipped year nine, they lose points in that year. But the way I have my system set up, using the formula above, a new participant that starts this year starts out with -18 points. That's not what we want.

So, I'm wondering, how can I get a formula like below to only start at the first cell that has a value:

(=COUNTIF(C2:L2,0)

I hope this makes sense.

Company Name	2009	2010	2011	2012	2013	2014	2015	2016	2017	2018	Missed	Points
A Company	0	0	0	0	0	0	100	100	100	100	6	-8
B Company	100	100	100	100	100	100	100	100	100	0	1	7
C Company	100	100	100	0	100	100	100	0	100	100	2	4

<tbody>
</tbody>

 

[jtakw]

Hi,

If I understand correctly, you can do this:

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Company Name	2009	2010	2011	2012	2013	2014	2015	2016	2017	2018	Missed	Points
2	A Company	0	0	0	0	0	0	100	100	100	100	0	4
3	B Company	100	100	100	100	100	100	100	100	100	0	1	7
4	C Company	100	100	100	0	100	100	100	0	100	100	2	4
Sheet19

Cell Formulas
Range	Formula
M2	=COUNTIFS(C$1:L$1,IFERROR(">"&LOOKUP(1,C2:L2,C$1:L$1),"<>"),C2:L2,0)
N2	=COUNTIF(C2:L2,100)-M2*2

M2, N2 formulae copied down.

 

[timfear]

Thank you. This looks promising, but didn't quite work. I think perhaps my examples wasn't real world. Hopefully the example below will make it clearer. Please forgive me - I cannot figure out how to paste it in a more readable fashion like yours below:

Company Name	2009	2010	2011	2012	2013	2014	2015	2016	2017	2018	Missed	Points
A Company	200	0	0	0	0	0	0	0	0	0	9	-16
B Company	0	0	0	0	0	0	200	200	100	0	1	3
C Company	0	0	0	0	0	0	600	600	0	0	2	8
D Company	600	0	600	0	600	0	600	0	600	0	5	20

<tbody>
</tbody>

So, Company A had a value of 200 in 2009 but never participated again. They got 2 points (1 per 100) for 2009, then had 2 points subtracted for the next 9 years giving them -16 points.

Company B didn't start until 2015 so they have no penalty for 2009-2014. Their counting starts in 2015. They have 5 points, then had 2 subtracted for missing 2018, giving them 3 points.

Company D missed every other year. They get 6 points for the year they are in, but lose 2 for the years they skipped.

I hope this makes sense. Please let me know if you have any ideas. Either way, thanks again for your help.

Hi,

If I understand correctly, you can do this:

	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Company Name	2009	2010	2011	2012	2013	2014	2015	2016	2017	2018	Missed	Points
2	A Company	0	0	0	0	0	0	100	100	100	100	0	4
3	B Company	100	100	100	100	100	100	100	100	100	0	1	7
4	C Company	100	100	100	0	100	100	100	0	100	100	2	4

<tbody>
</tbody>

Sheet19​

Worksheet Formulas Cell Formula M2 =COUNTIFS(C$1:L$1,IFERROR(">"&LOOKUP(1,C2:L2,C$1:L$1),"<>"),C2:L2,0) N2 =COUNTIF(C2:L2,100)-M2*2 <tbody> </tbody>

<tbody>
</tbody>

M2, N2 formulae copied down.

 

[Tetra201]

Try

=COUNTIF(INDEX(C2:L2,MATCH(TRUE,INDEX(C2:L2>0,0),0)):L2,0) for "Missed"
=SUM(C2:L2)/100-COUNTIF(INDEX(C2:L2,MATCH(TRUE,INDEX(C2:L2>0,0),0)):L2,0)*2 for "Points"

 

[jtakw]

Thank you. This looks promising, but didn't quite work. I think perhaps my examples wasn't real world. Hopefully the example below will make it clearer.
I hope this makes sense. Please let me know if you have any ideas. Either way, thanks again for your help.

You're welcome, no problem, based on the additional information and sample you provided, it's not a difficult fix:

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Company Name	2009	2010	2011	2012	2013	2014	2015	2016	2017	2018	Missed	Points
2	A Company	200	0	0	0	0	0	0	0	0	0	9	-16
3	B Company	0	0	0	0	0	0	200	200	100	0	1	3
4	C Company	0	0	0	0	0	0	600	600	0	0	2	8
5	D Company	600	0	600	0	600	0	600	0	600	0	5	20
Sheet22

Cell Formulas
Range	Formula
M2	=IF(C2=0,COUNTIFS(C$1:L$1,IFERROR(">"&LOOKUP(1,C2:L2,C$1:L$1),"<>"),C2:L2,0),COUNTIF(C2:L2,0))
N2	=SUM(C2:L2)/100-M2*2

 

[timfear]

Thank you so much, worked like a charm.

 

[Tetra201]

You are welcome; which one?

 

[jtakw]

You're welcome.