Calculate the running count in Excel
Running counts are handy for keeping track of how many times a value has occurred consecutively. They are used across numerous fields to monitor progress, analyse trends, and manage resources.
In the video example, a list of football results is displayed in A2:B17, featuring a column for the score and another for result classification — 'W' for win, 'D' for draw, and 'L' for loss.
The aim is to count each successive win and only reset the counter when a streak is broken. There are a couple of ways to do this.
𝗧𝗿𝗮𝗱𝗶𝘁𝗶𝗼𝗻𝗮𝗹 𝗠𝗲𝘁𝗵𝗼𝗱
Formula in C3:
=𝖨𝖥(𝖡𝟥="𝖶",
𝖨𝖥(𝖡𝟥=𝖡𝟤,
𝖢𝟤+𝟣,𝟣),
𝟢)
The opening IF statement checks to see if the current result in B3 equals "W". If TRUE, it proceeds to another IF statement, which looks at whether it's equal to the one above in B2. If TRUE, 1 is added to the previous winning streak number in C2. If FALSE, 1 is returned.
0 is the FALSE value for the first IF statement to ensure draws and losses are not included in any counts.
This formula is populated downwards for the other 14 rows to return the correct results, helped by the relative row references.
𝗠𝗼𝗱𝗲𝗿𝗻 𝗠𝗲𝘁𝗵𝗼𝗱
Formula in D3:
=𝖲𝖢𝖠𝖭(𝟢,
𝖡𝟥:𝖡𝟣𝟩,
𝖫𝖠𝖬𝖡𝖣𝖠(𝖺,𝖻,𝖨𝖥(𝖻="𝖶",𝖺+𝟣,𝟢))
)
The second approach is based on a single formula that revolves around the SCAN function, which is ideal for running counts, running totals, and anything else that involves intermediate values.
An 𝗂𝗇𝗂𝗍𝗂𝖺𝗅_𝗏𝖺𝗅𝗎𝖾 of 0 determines the starting point, while the 𝖺𝗋𝗋𝖺𝗒 points to the results in B3:B17.
The final argument requires a LAMBDA to specify the calculation that'll take place. The parameters a and b represent the accumulator and current result, respectively.
The IF statement checks if the current result (b) is equal to "W". If TRUE, 1 is added to the accumulator (a) and 0 if FALSE.
𝗢𝗻𝗲 𝗺𝗼𝗿𝗲 𝘁𝗵𝗶𝗻𝗴…
While it's easy to take a quick glance at the new columns and spot that 5 is the highest winning streak, you might want to calculate this in a cell.
It's a simple case of wrapping either range inside the MAX function. E.g. =𝖬𝖠𝖷(𝖢𝟥:𝖢𝟣𝟩).
#exceleration #excel #globalexcelsummit
---
The Global Excel Summit is the world's largest gathering of Microsoft Excel users and experts.
Find us on:
🌐 Website: https://globalexcelsummit.com
👔 LinkedIn: / global-excel-summit
🆇 X: https://x.com/ExcelSummit
🤳 TikTok: / globalexcelsummit
📸 Instagram: / globalexcelsummit
🟦 Facebook: / globalexcelsummit
🧵 Threads: https://www.threads.net/@globalexcels...