Hello All,
I have requirement where I have to count number of columns which has a consecutive values(Stop at Null) this is monthly activity, which means, every month we will have a new column and formula should automatically consider that too. All these countable columns are number fields.
below is the data.
Sales Account | 01-03-2023 | 01-04-2023 | 01-05-2023 | 01-06-2023 | 01-07-2023 | Comments | Status |
ABC | 2773523.67 | 2422687.25 | 1527363.89 | 21940.7 | 2320070 | Ignore | Active |
DEF | 6721623.6 | 742191 | XYZ | Active | |||
GHI | XYZ | Inactive | |||||
JKL | 54963.3463 | 36503.9697 | 241804.4598 | 35480.9466 | XYZ | Active | |
MNO | 83249.6127 | XYZ | Inactive | ||||
PQR | 254801.593 | 620222.866 | XYZ | Inactive | |||
STU | 61496.6072 | 552791.264 | 215380.8 | 83746.769 | XYZ | Active | |
VWX | 122977.92 | 811831.18 | XYZ | Inactive | |||
YZ1 | 75610.5636 | 47653.1286 | XYZ | Inactive |
I need a result like below.
Group Sales | 01-03-2023 | 01-04-2023 | 01-05-2023 | 01-06-2023 | 01-07-2023 | Count | Comments | Status |
ABC | 2773523.67 | 2422687.25 | 1527363.89 | 21940.7 | 2320070 | 5 | Ignore | Active |
DEF | 6721623.6 | 742191 | 2 | XYZ | Active | |||
GHI | 0 | XYZ | Inactive | |||||
JKL | 54963.3463 | 36503.9697 | 241804.4598 | 35480.9466 | 4 | XYZ | Active | |
MNO | 83249.6127 | 0 | XYZ | Inactive | ||||
PQR | 254801.593 | 620222.866 | 0 | XYZ | Inactive | |||
STU | 61496.6072 | 552791.264 | 215380.8 | 83746.769 | 2 | XYZ | Active | |
VWX | 122977.92 | 811831.18 | 0 | XYZ | Inactive | |||
YZ1 | 75610.5636 | 47653.1286 | 0 | XYZ | Inactive |
Solved! Go to Solution.
Similar to @Christina_H !
EDIT: Whoops, misread! You mentioned consecutive numbers - Missed that point!
Hello @Christina_H thank you so much, solution worked for me.