0

Greetings and salutations! I have a dataset as below. I need to count the number of times each unique Custom Id value is associated with a pair of Move to Thaw/Move to Freeze (known as freeze thaw cycles). For example, the first Custom ID of BS9583621 should have a count of 2, in fact all these in the example image should (but the list is very long and there will be hundreds with more or less than 2).

I tried something like this that I found online for a similar question someone else asked:

=IF(A2<>A1,COUNTIF(A:A,A2),"")

But it's essentially counting the number of times the Custom ID appears.

enter image description here

JvdV
  • 70,606
  • 8
  • 39
  • 70
RaleighWalker
  • 17
  • 5
  • 1
    I suppose you can have data with 'Move To Thaw', 'Move to Freeze', 'Move To Thaw' but don't complete their 2nd cycle, meaning the count would remain 1? And if these cycles are succesive, would the assumption that any even number of lines per unique ID could be devided by two to get the count you are after? – JvdV Feb 01 '22 at 15:36
  • Does this answer your question? [How to select a part of a range, which is output of a formula](https://stackoverflow.com/questions/70941367/how-to-select-a-part-of-a-range-which-is-output-of-a-formula) – Dominique Feb 01 '22 at 15:36
  • @JvdV - I'm afraid I'm not quite understanding the first part of your answer. 1 complete cycle would be "Move to Thaw", "Move to Freeze", and a 2nd complete cycle would be a 2nd instance of Move to Thaw, Move to Freeze. Some of these IDs will have up to 15 cycles. I do think it can be assumed that an even number of lines per unique ID could be divided by 2. – RaleighWalker Feb 01 '22 at 15:44
  • @RaleighWalker, what I meant: What are the odds that any of these unique ID's have an odd number of lines? Essentially meaning, there are uncompleted cycles if one assumes that any cycle would be two lines of data. – JvdV Feb 01 '22 at 15:45
  • In that case, `=FLOOR(COUNTIF(custom_id_range,specific_custom_id)/2)` where the `custom_id_range` is the first column in your data and `specific_custom_id` is a specific value from that column, presumably in a row header of a summary table. – FlexYourData Feb 01 '22 at 15:49
  • @JvdV - I gotcha- thanks. There probably will be a few that either don't have the final Move to Freeze, or have an extra Move to Thaw due to human error. I assumed I would have to manually scroll through the data to find any "results" that look off. There will be several dozen with odd numbers of complete cycles. – RaleighWalker Feb 01 '22 at 16:00
  • @FlexYourData Thanks- I'm going to try this shortly. Meeting just started... – RaleighWalker Feb 01 '22 at 16:00

0 Answers0