I'm trying to do a countif using the sumproduct function to only count visible rows. However, it gives me the exact number as if there is no filter at all. Could you please help check my formula? Thanks!
| Date | Name | Available? |
|---|---|---|
| Monday | Person 1 | Yes |
| Monday | Person 2 | Yes |
| Tuesday | Person 1 | No |
| Tuesday | Person 2 | Yes |
| ......... |
All Date, Name and Available? has data filter on them. When I filter the table to look at Tuesday only, I'd like to count how many Yes'es are on Tuesday.
I should give me the result = 1. However, my formula gives result = 3, which is wrong :(
What I wrote
=SUMPRODUCT((J7:J1000= "Yes")*(SUBTOTAL(103,OFFSET(J7,ROW(J7:J1000)-MIN(ROW(J7:J1000)),0))))
You may try with this formula. It uses SUBTOTAL with option 103 (COUNTA) to filter out the rows that are hidden. And put that filtered range inside COUNTIF:
=COUNTIF(FILTER(J2:J,BYROW(J2:J,LAMBDA(e,SUBTOTAL(103,e)))),"yes")
Try it and let me know!