Forum Discussion
Solved
Count the occurrence of 3's and 4's in a column with hidden rows
In excel if you want to count the occurrence of two numbers in a column you can use:
=COUNTIF(Table13[Amada],3)+COUNTIF(Table13[Amada],4)
and that will return the number of times that the digit 3 and the digit 4 appeared in that column.
but how do you do the same thing if some rows are hidden and you want to ignore the hidden rows?
countif does not ignore hidden rows.
Subtotal and aggregate have settings to ignore hidden rows, but I can't figure out the syntax with those functions.
Thanks,
If you have access to LAMBDA:
=COUNT(IF(SCAN(,Table13[Amada],LAMBDA(a,b,IF(SUBTOTAL(102,b)>0,b,0)))={3,4},1))
4 Replies
If you have access to LAMBDA:
=COUNT(IF(SCAN(,Table13[Amada],LAMBDA(a,b,IF(SUBTOTAL(102,b)>0,b,0)))={3,4},1))- This works great,
Thank you!
=SUMPRODUCT((Table13[Amada]=3)*(SUBTOTAL(103,OFFSET(Table13[[#Headers],[Amada]],ROW(Table13[Amada])-MIN(ROW(Table13[Amada]))+1,0,1)))) + SUMPRODUCT((Table13[Amada]=4)*(SUBTOTAL(103,OFFSET(Table13[[#Headers],[Amada]],ROW(Table13[Amada])-MIN(ROW(Table13[Amada]))+1,0,1))))
- Thanks,
I appreciate you taking the time to give me a response. I tried it and it works great.
