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

T

timfear

New Member
Joined
Mar 14, 2017
Messages
4
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.

[TABLE="width: 626"]
<tbody>[TR]
[TD]Company Name[/TD]
[TD]2009[/TD]
[TD]2010[/TD]
[TD]2011[/TD]
[TD]2012[/TD]
[TD]2013[/TD]
[TD]2014[/TD]
[TD]2015[/TD]
[TD]2016[/TD]
[TD]2017[/TD]
[TD]2018[/TD]
[TD]Missed[/TD]
[TD]Points[/TD]
[/TR]
[TR]
[TD="align: left"]A Company[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]-8[/TD]
[/TR]
[TR]
[TD="align: left"]B Company[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: left"]C Company[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
Hi,

If I understand correctly, you can do this:


Book1
BCDEFGHIJKLMN
1Company Name2009201020112012201320142015201620172018MissedPoints
2A Company00000010010010010004
3B Company100100100100100100100100100017
4C Company1001001000100100100010010024
Sheet19
Cell Formulas
RangeFormula
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.
 
Last edited:
Upvote 0
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:

[TABLE="width: 545"]
<tbody>[TR]
[TD]Company Name[/TD]
[TD]2009[/TD]
[TD]2010[/TD]
[TD]2011[/TD]
[TD]2012[/TD]
[TD]2013[/TD]
[TD]2014[/TD]
[TD]2015[/TD]
[TD]2016[/TD]
[TD]2017[/TD]
[TD]2018[/TD]
[TD]Missed[/TD]
[TD]Points[/TD]
[/TR]
[TR]
[TD="align: left"]A Company[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]-16[/TD]
[/TR]
[TR]
[TD="align: left"]B Company[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: left"]C Company[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: left"]D Company[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]20[/TD]
[/TR]
</tbody>[/TABLE]


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.


jtakw said:
Hi,

If I understand correctly, you can do this:

BCDEFGHIJKLMN
Company NameMissedPoints
A Company
B Company
C Company

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]2009[/TD]
[TD="align: right"]2010[/TD]
[TD="align: right"]2011[/TD]
[TD="align: right"]2012[/TD]
[TD="align: right"]2013[/TD]
[TD="align: right"]2014[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]2018[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]

</tbody>
Sheet19​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M2[/TH]
[TD="align: left"]=COUNTIFS(C$1:L$1,IFERROR(">"&LOOKUP(1,C2:L2,C$1:L$1),"<>"),C2:L2,0)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]N2[/TH]
[TD="align: left"]=COUNTIF(C2:L2,100)-M2*2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



M2, N2 formulae copied down.
Click to expand...
 
Upvote 0
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"
 
Upvote 0
timfear said:
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.
Click to expand...

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


Book1
BCDEFGHIJKLMN
1Company Name2009201020112012201320142015201620172018MissedPoints
2A Company2000000000009-16
3B Company000000200200100013
4C Company0000006006000028
5D Company60006000600060006000520
Sheet22
Cell Formulas
RangeFormula
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

SFCChase
  • Question Question
Projected GPA
2
Replies
15
Views
1K
KRice
K
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
R
  • Solved
Countifs formula with criteria comparing date with year
Replies
4
Views
565
Fluff
Fluff
A
  • Solved
Calendar issue
Replies
1
Views
2K
AndyEd
A
E
  • Question Question
Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.
Replies
3
Views
3K
profray
P

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top