Countif unless value present, then start count over

B

brandon20

Board Regular
Joined
Feb 12, 2014
Messages
203
Hi,

I need to count how many times a rig occurs in my spreadsheet in consecutive order,
I currently have a countif equation that works for this.
I wanted to add an argument so the function will not count if there is a start value present in column B BUT I also need the function to start the count over for this rig.

I have shown an example of what I am looking for below.

Thanks


[TABLE="width: 462"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Rig[/TD]
[TD]Start[/TD]
[TD]Countif[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]5/1/2015[/TD]
[TD]=COUNTIF($A$2:A2,A2)[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]


Example of what I am looking for
[TABLE="width: 462"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Rig[/TD]
[TD]Start[/TD]
[TD]Countif[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]5/1/2015[/TD]
[TD]RR[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
Does this help?


Excel 2010
ABCDE
1RigStartCountifDesiredFormula
2A05/01/20151RRRR
3B111
4C111
5D111
6A211
7B222
8C222
9D222
10A06/01/20153RRRR
11B333
12C333
13D333
14A411
15B444
16C444
17D444
18A522
19B555
20C555
21D555
Sheet1
Cell Formulas
RangeFormula
E2=IF(B2>0,"RR",COUNTIFS($A$2:A2,A2,$B$2:B2,"")-MAX(COUNTIFS($A$2:A2,A2,$B$2:B2,">0")-1,0))
 
Upvote 0
Hi,

Thanks! that almost works,
but it seems to subtract values as more start dates add to the sheet
I need this to just start at 1 again once a start date is added
I gave an example of what of what i would desire.

Thanks!






[TABLE="width: 389"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Rig[/TD]
[TD]Start[/TD]
[TD]Desired[/TD]
[TD][/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]5/1/2015[/TD]
[TD]RR[/TD]
[TD][/TD]
[TD]RR[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]6/1/2015[/TD]
[TD]RR[/TD]
[TD][/TD]
[TD]RR[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]7/1/2015[/TD]
[TD]RR[/TD]
[TD][/TD]
[TD]RR[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD]6[/TD]
[TD][/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD]6[/TD]
[TD][/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD][/TD]
[TD]6[/TD]
[TD][/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
I need to get better at this stuff. This doesn't look very nice and there is probably a better way but this seems to work:


Excel 2010
ABCD
1RigStartDesiredFormula
2A05/01/2015RRRR
3B11
4C11
5D11
6A06/01/2015RRRR
7B22
8C22
9D22
10A07/01/2015RRRR
11B33
12C33
13D33
14A11
15B44
16C44
17D44
18A22
19B55
20C55
21D55
22A33
23B66
24C66
25D66
Sheet2
Cell Formulas
RangeFormula
D2=IF(B2>0,"RR",COUNTIF($A$2:A2,A2)-IFERROR(COUNTIF(OFFSET($A$1,0,0,SUMPRODUCT(MAX(($A$2:A2=A2)*ROW($A$2:A2)*($B$2:B2<>""))),1),A2),0))
 
Upvote 0
Thanks! not that I understand how it works though. haha.
OFFSET and SUMPRODUCT are confusing to me. I will attempt to reverse engineer this to see how it functions.
I added another layer to the equation, now I have start and end dates, it works perfectly for what I am trying to do. (at the moment). Thanks again!


[TABLE="width: 628"]
<colgroup><col width="157" span="4" style="width:118pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 157"]Rig[/TD]
[TD="class: xl65, width: 157"]start[/TD]
[TD="class: xl65, width: 157"]End[/TD]
[TD="class: xl65, width: 157"]Formula[/TD]
[/TR]
[TR]
[TD="class: xl65"]A[/TD]
[TD="class: xl66"]5/10/2015[/TD]
[TD="class: xl66"]5/25/2015[/TD]
[TD="class: xl67"]RR[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl66"]5/20/2015[/TD]
[TD="class: xl66"]5/29/2015[/TD]
[TD="class: xl67"]RR[/TD]
[/TR]
[TR]
[TD="class: xl65"]C[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"]D[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"]A[/TD]
[TD="class: xl66"]6/1/2014[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"]C[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"]2[/TD]
[/TR]
[TR]
[TD="class: xl65"]D[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"]2[/TD]
[/TR]
[TR]
[TD="class: xl65"]A[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"]2[/TD]
[/TR]
[TR]
[TD="class: xl65"]C[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"]3[/TD]
[/TR]
[TR]
[TD="class: xl65"]D[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"]3[/TD]
[/TR]
[TR]
[TD="class: xl65"]A[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"]2[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"]3[/TD]
[/TR]
[TR]
[TD="class: xl65"]C[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"]4[/TD]
[/TR]
[TR]
[TD="class: xl65"]D[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"]4[/TD]
[/TR]
[TR]
[TD="class: xl65"]A[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"]3[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"]4[/TD]
[/TR]
[TR]
[TD="class: xl65"]C[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"]5[/TD]
[/TR]
[TR]
[TD="class: xl65"]D[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"]5[/TD]
[/TR]
</tbody>[/TABLE]




[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]=IF(C2>0,"RR",IF(B2>0,0,COUNTIF($A$2:A2,A2)-IFERROR(COUNTIF(OFFSET($A$1,0,0,SUMPRODUCT(MAX(($A$2:A2=A2)*ROW($A$2:A2)*($B$2:B2<>""))),1),A2),0)))[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
I'm glad it works, but it's by no means the only answer I'm sure.

Example using Rig = A

Count all the values of A up to the formula row. (Count1)

The sumproduct finds the last row where column B is not blank (has a date) up to the row containing the formula.
The Offset creates a range starting in A1 and finishing at the row found by sumproduct.
Count all the values of A in this new range. (Count2)

The Formula result is Count1 - Count2
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Number series based on multiple criteria
Replies
3
Views
197
SanjayGMusafir
SanjayGMusafir
L
  • Solved
Formula in the helper column. need duplicate to be count as One
Replies
7
Views
431
KRice
K
R
  • Solved
Excel/Google Sheets COUNTIFS formula to count rows, but with a substring in the Range.
2
Replies
11
Views
574
Fluff
Fluff
R
  • Solved
Count of consecutive months same data value appears
Replies
1
Views
187
rhonllanders
R
L
  • Solved
Formula helper Column Join two cell and Countif first, second, third etc.
Replies
3
Views
442
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

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