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])))
Hello Anonymous ,
You can try the following formula:
COUNTIFS =
CALCULATE (
COUNTROWS ( Table ),
FILTER (
Table,
Table[A] = [A]
&& Table[B] = [B]
&& Table[C] = [C]
)
)
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])))Hello themistoklis , but my problem is not only this - counting how many time does the same condition appears, but I'm interested in increasing this counting every time it repeats.
If you can see lines 3 and 4 are equals when you are comparing Columns A to C.
The columns D is counting if the previous columns combinations already appeared earlier (rows 1, 2 and 3).
For line 3, is the first time that this condicion (Other, 1282879 and 2.000,00) appeared (so, Column D = 1).
For line 4, is the second time that this conditions appeared (so Columns D = 2)
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