Forum Discussion
Count ifs with variable range
Hello Community, I'm using a Count ifs in excel like this =COUNTIFS($C$1:C18;C18;$A$1:A18;A18;$B$1:B18;B18) Notice that I have a variable range that changes with the rows. I need to do the s...
I solved it.
CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
'Table'[A] = EARLIER('Table'[A])
&& 'Table'[B] = EARLIER('Table'[B])
&& 'Table'[C] = EARLIER('Table'[C])
&& 'Table'[Index] <= EARLIER('Table'[Index])))
Anonymous , can you explain the logic, Difficult to understand excel formula
My problem here is that I need to know when was the first, secont, third, .... time the value, which contemplates such conditions appears.
Fot exemple, in row 4 from the example sent earlier, column D assumes value equals to 2, because is the second time that the value 2.000,00 apears for condictions Type = Other, Account.Num = 1382879, and value = 2.000,00 .
| A | B | C | D | |
| 1 | Type | Account.Num | Value | Count.ifs |
| 2 | Booking | 1382879 | 2.000,00 | 1 |
| 3 | Other | 1382879 | 2.000,00 | 1 |
| 4 | Other | 1382879 | 2.000,00 | 2 |
Line 16 - It is the third time that the value 2.000,00 appears with the condicionts Type = Booking, Account Num = 1536899 and value is 2.000,00
| 16 | Booking | 1536899 | 2.000,00 | 3 |