Booking Counter Issue

[David Lam]

Hi,

I have a issue that is very similar to the spreadsheet attached. Each day I get dumped raw data. I arrange it by name and date from earliest date to the latest date. I have been using countif up to this stage and it works. But now my issue is someone has suggested to split the bookings by topic and now my counter off. I am counting a booking on the same date as something new and my sequence it out. Is there another formula to fix this?

 

[AliGW]

COUNTIFS will allow you to specify extra crieria. Give it a go.

 

[David Lam]

Hi Ali, I believe if I insert another criteria in a COUNTIFS formula it will reset my counter. Do you have a specific criteria for this or example of this on the sample worksheet?

 

[AliGW]

OK - I see.

=IF(COUNTIF($K$9:$K9,K9)>1,N8,COUNTIF($K$9:$K9,K9))

 

[David Lam]

Slightly off. I give it another shot.

 

[AliGW]

"Thank you" would be nice.

Good luck with tweaking it yourself.

 

[David Lam]

"Thank you" would be nice.

Good luck with tweaking it yourself.

Thank you for the COUNTIFS.

 

[p45cal]

in Q9:
=IF(AND(K9=K8,L9>L8),Q8+1,IF(AND(K9=K8,L9=L8),Q8,1))
copied down gives tthe same results as your goal but I may have misunderstood the logic, so test it.

 

[David Lam]

Wow it works. Thank you p45cal. I'll see if the logic works on a bigger set of data. I have been heavily reliant on the COUNTIF formula so I was trying different combinations with AND and > but failed. Who knew it was so simple on your one.

 

[NARAYANK991]

Hi ,

The formula you posted did not make use of COUNTIFS ; if it had , it would have worked.

See the attached file.

Narayan

 

[David Lam]

Hi ,

The formula you posted did not make use of COUNTIFS ; if it had , it would have worked.

See the attached file.

Narayan

That would be correct, I had been using COUNTIF up to this point. It was working in the past until we decided to separate the bookings by topic. It should treat multiple bookings on the same day as 1 booking. My issue is the COUNTIF formula was treating each line as a booking, so I am double counting multi-sameday bookings. Sorry for the confusion. p45cal one seems to be working. I haven't had the chance to dump a big data set on it.

 

[NARAYANK991]

Hi ,

Try this then :

=IF(COUNTIFS(K$9:K9,K9,L$9:L9,L9)>1,R8,IF(COUNTIF(K$9:K9,K9)=1,1,R8+1))

Enter this in R9 and copy down.

Narayan

 

[bosco_yip]

in Q9:
=IF(AND(K9=K8,L9>L8),Q8+1,IF(AND(K9=K8,L9=L8),Q8,1))
copied down gives tthe same results as your goal but I may have misunderstood the logic, so test it.

And this shorter formula produce the result same as p45cal's formula.

=IF(K9<>K8,1,IF(L9=L8,Q8,N(Q8)+1))

Regards
Bosco

 

[David Lam]

Thank you everyone it works on my raw data. I will take everyone advice in for self learning

 

[p45cal]

=IF(K9<>K8,1,IF(L9=L8,Q8,N(Q8)+1))

or even:
=IF(K9=K8,Q8+N(L9<>L8),1)

 

[bosco_yip]

or even:
=IF(K9=K8,Q8+N(L9<>L8),1)

Remove the N() can produce the same result :

=IF(K9=K8,Q8+(L9<>L8),1)

Regards
Bosco