Counting cells by group

[StevieW]

Hi o/

First post here. Wanted to ask here as whenever I google for excel help, a lot of the time the best answers I get are from this forum. This time however, I'm stumped (or tired).

qwe.PNG

In the example above, I have a tracker I use at work to monitor holidays, absence, etc. The red cells with a 4 relate to absence. I can count how many days absence easy enough with countif and I get 4 for example on line 26, but I also want to count the number of periods of absence, which in this case would be 3.

Can' think how to do this? I could merge the cells, but then I lose the ability to count the total days?

Thanks in advance for any help you folks can provide

[Phuocam]

Try this ...

=COUNTIFS(B26:X26,"1",C26:Y26,"")

[BadlySpelledBuoy]

Phuocam beat me to it, but as I put the file together I'll post it anyway.

BSB

[StevieW]

That's brilliant, thank you both very much.

Can't say as I fully understand why it works, but I'm at work and rushing so will make it make sense at home tonight!

Thanks again, both very helpful, and thanks also BSB for the file

Edit : Lightbulb moment! It counts the occurrences of the end of the group, ie any 1's with only blank cells afterwards! Easy when you know how!

[AliGW]

The COUNTIFS function has to fulfil both criteria: in other words, it is counting only the ultimate cell in each range, as the next cell has to be blank (""). It's a nifty trick!

[BadlySpelledBuoy]

It works by counting the number of cells in a range that are contain a 1 but have a blank cell to the right of them. Notice the two ranges in the formula are offset by 1 column.
So if you have 3 cells in a row with a 1 in them it's only counting the last one as this one has a blank cell to the right and ignoring the other two as they do not have a blank cell to the right.

Hopefully that helps explain it a little.

BSB

[BadlySpelledBuoy]

It's a nifty trick!

Nifty indeed!
Sadly I cannot take the credit for it. I pinched it off daddylonglegs many moons ago when I was trying to do exactly the same thing at work.
http://www.excelforum.com/excel-form...-in-excel.html

BSB

[StevieW]

Christ, you folks are quick, beat my edit

Found a problem though in the way I would use. I use other references, such as "4" for annual leave. If there is a 4 after a 1, it wouldn't work would it? Any way to make it so it only counts if NOT a 1?

[BadlySpelledBuoy]

Here you go.

BSB

[StevieW]

Of course! Thanks again I like it here lol

[BadlySpelledBuoy]

I like it here lol

Me too. That's why I spend so much time here even when I'm meant to be working!

BSB

[AliGW]

Devil's advocate: what if there is one day of leave immediately after a period of absence???

PS I like it here, too, but only get to visit often when I'm NOT at work!!!

[BadlySpelledBuoy]

Devil's advocate: what if there is one day of leave immediately after a period of absence???

You've lost me there...

BSB

[StevieW]

The "<>1" addition to the second range in the countifs solves that, doesn't it?

[BadlySpelledBuoy]

The "<>1" addition to the second range in the countifs solves that, doesn't it?

I don't know as I'm not 100% sure what AliGW meant...

BSB

[AliGW]

You've lost me there...

BSB

The "<>1" addition to the second range in the countifs solves that, doesn't it?

I don't know as I'm not 100% sure what AliGW meant...

BSB

I was having a blonde moment ... Just ignore me.

Sorry, boys!