COUNTIF each occurrence and subtract 2 from each occurence

[lorileirn]

Trying to calculate the number of "T's" in a table row and I do not want to count the first or the last occurrence, so in essence I want to subtract 2 from whatever my total is. I have that working, maybe not efficiently, but it is working.

Here is what I have:

=(COUNTIF(tblJanuary[@[1]:[31]],"T")-2) - this is calculated in hidden columns.

=IF(AQ5<=0,0,AQ5) - this is in the visible columns

There is a possibility that there could be a break in the occurrences. In other words, person leaves and is gone 5 days and comes back for 2 days and then is gone again for 4 days. I would need to subtract 2 from each of the instances and only count the number of days less the first and last day of each occurrence. How do I count/calculate each occurrence and subtract the first and last day of each?


I hope that makes sense.
Thank you so much,
Lori

 

[DILIPandey]

Hi Lori,

What if person gone for 2 days only ?
Can you share a sample data with your expected output ?



Regards,
DILIPandey

 

[lorileirn]

I tried to post a picture with this and had a difficult time. However, they wouldn't take only 2 days, they would take at least 3 and most of the time 5.
When I get back to my desk I will try again to post a picture.
Thank you.

 

[lorileirn]

Hoping this dropbox link works with the picture. Thank you.

 

[lorileirn]

https://www.dropbox.com/s/fvfjp6ixx2dnktp/Leave.JPG?dl=0
Hoping this link works with the picture. Thank you.

 

[DILIPandey]

Okay.. I saw the picture and seems to be simple one... just countif for "T" and subtract 2 from the result and I guess you already achieved this.

I would need to subtract 2 from each of the instances and only count the number of days less the first and last day of each occurrence.

If you have instances of t in range A7:O7, use below formula p7:-

=COUNTIF($A$7:$O$7,"t")-SUMPRODUCT((FREQUENCY(IF($A$7:$O$7="T",COLUMN($A$7:$O$7),""),IF($A$7:$O$7<>"T",COLUMN($A$7:$O$7),""))>0)*1)*2

Confirm formula with key combination ctrl shift enter.


Regards,
DILIPandey

 

[lorileirn]

DILIPandey,
That worked Perfectly!!!
Thank you so much!!
Lori

 

[DILIPandey]

you are welcome lorileirn.... Cheers



Regards,
DILIPandey