Help with a conditional count to avoid counting repeated subsequent occurences...?

Hi all,

I have a table where I want to perform a count.

It basically measures a value at time intervals 10:00, 11:00, 12:00, ..., 16:00. The first requirement is a Yes at 16:00. If it's a Yes at 16:00, I want to count where the Yes first happened.

If the condition is fulfilled at say 10:00, it will be fulfilled at the later intervals also. I'm basically interested in knowing and counting in which interval it happened and don't do a double count.

On the first row, you'll see that a Yes was first recorded at 12:00 and then at subsequent time slots also. I've marked yellow to show where it first happened. For that row, I want to make a count only at 12:00 and ignore the rest of the day/row.

The right is a matrix where I just did a manual count now to show what I mean. I want to count where it first happens.

Uten navn.png

Is there some kind of general formula which can be implemented to perform this count?

I would prefer to not set up a matrix as show to the right. It was just an example I made to show how I want to count.

Ideally, I'll just have slots lined up as in the header and then have a frequency count at each associated cell below like this:

Uten navn1.png

Thanks very much in advance!

If needed, I can set up my tables differently. But my basic raw data is columns with Yes/No at various time slots so that's what I have to work with.

Thanks a lot guys! Both work, but I was indeed wishing to not have a matrix, but merely a count in one single row.

I haven't checked it in detail as I'm at work, but looks like Dave nailed it here.

Originally Posted by FlameRetired

I interpreted a little differently. This does not require the matrix in N14:T24.

In N26 and filled to the right.

Formula:

Please Login or Register  to view this content.

This formula is currently a bit above my head, so I don't fully understand it.

But can OFFSET be implemented with this one?

I typically use OFFSET on all my statistical formulas in order to have a dynamic lookback period where OFFSET references a cell value (5, 10, 15, etc.)

Thanks in advance.

Elijah

Thanks a lot everyone. Great work!

Could OFFSET be utilized for these formulas in order to have a dynamic lookback period?

Cell G4 shows how I've implemented a count for "Yes" at 10:00 using an OFFSET value in the cell above.

Could the same be done for this formula?

Thanks in advance.

EDIT: I did try it on the MMULT, but it did not work out correctly...

Originally Posted by Bo_Ry

I guess 15 mean 15:00

Thank you, Bo_Ry!

15 is not 15:00, but should refer to number of rows. The OFFSET starts at Row 14. So OFFSET value 15 means count from Row 14 and 15 rows down. : )
A typical dataset for me is 1500 rows. Often I would want to check maybe the top 15 rows. Then I would use OFFSET 15. Or 10.
Does that change your implementation of the formula?

Hi, Bo,

I'm at work, so could only test it very briefly, but changing values in the input cell G3 does not seem to make changes to calculated values?

Originally Posted by Bo_Ry

Please try to change G3 to 24 and F4 to "# Yes @ 10:00" and see

Hi,

Thank you!

Why G3 = 24?

Also, I didn't intend on using "# Yes @ 10:00" as input. That was just a text line showing the calculation I first made to count the # Yes @ 10:00 using OFFSET.

My wish is to have the OFFSET function implemented for the entire row you first created in post #6.

Maybe I have what I need to figure this out on my own. I can check when I'm back from work.

Best regards and thanks again!

Elijah

Hi, Bo_Ry,

Many thanks again.

I tried to implement your OFFSET, but couldn't quite figure it out. I have now taken your elegant formula and implemented it for my entire table.

Glenn Kennedy was also right in that it is very good for avoiding a count when there is no "Yes" at 16:00. I know I wrote that. The plan was to somehow filter out the No values at 16:00. But this way is much better.

I've applied your MMULT formula to cells B9:H9. It currently works great for the entire table.

Uten navn.png

In Cell J9 I'm doing a count of column I based on an OFFSET formula and the cell value in B3.

It is the same general formula I would like to implement for the formula in cell B9:H9.

I've decided that this is not very important for me, but if it's possible, why not.

Anyway, thank you for all help! Really appreciated.

Best regards,

Elijah

Originally Posted by Bo_Ry

Glad that you can apply.

If you still need offset, Please try at

B14:I14
=SUMPRODUCT(--(MMULT(--(OFFSET($B21:B21,,,$B$3)="Yes"),ROW(INDIRECT("1:"&COLUMNS($B21:B21)))^0)=1))

Amazing!

Works great.

I wish I could give you more reputation, but not allowed (yet).

Thanks again for all help!

Elijah

I added another related column labeled Open in B21. This is simply numerical data, positive and negative.

I would be interesting to me to see if there's any differences between a positive and a negative open when counting # Yes.

Uten navn.png

I tried to implement this by using IFS, but I get an error:

Please Login or Register  to view this content.

Is it possible somehow?

The best would be to use the drop down menu in Cell B5 with possible values: positive, negative and all.

Positive = >0
Negative = <0
All = all days in the data set

I was able to implement the formula for All days as that one obviously is easy.

Please Login or Register  to view this content.

Not sure if the conditional count is possible when the formula is this complex? I imagined I could do it with IF, but didn't quite nail it down.

I have other ways to find out this result. I can filter data and copy and paste it. But easier for the future with a formula if possible.

Best regards and thanks.

Elijah

Amazing, Bo_Ry!

That worked out great. I'm very impressed. When I get time on Sunday, I will sit down with that formula and try to actually understand it.

Thank you for all your help and have a great day!

Best regards,

Elijah