Hello - newby here
Was reading the forum as a guest in relation to a query I had in relation to the use of COUNTIF when filtering. I am pleased to report I solved it with the help of a 4-year old post, which introuced me to SUMPRODUCT.
In solving my initial query, it threw up another... This again was to do with filter results.
The original query was in regards to how to count a number of cells that contained "DL", but I also wanted to filter the data is it is going to be analyised in months. The resolution looks as follows:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(G2,ROW($G$3:$G31)-ROW($G$2),,1)),--($G$3:$G31="DL"))
I then created a forumla that counts the number of Pass cells for DL, which looks as follows:
=SUMPRODUCT((G3:G31="DL")*(H3:H31="Pass"))
My issue is that when I filter out the data, the results of my new formila don't change to reflect the data that is shown. I need to add a "SUBTOTAL" to the formula, similar to my first, but I am stumped as to how.
Any thoughts?
Was reading the forum as a guest in relation to a query I had in relation to the use of COUNTIF when filtering. I am pleased to report I solved it with the help of a 4-year old post, which introuced me to SUMPRODUCT.
In solving my initial query, it threw up another... This again was to do with filter results.
The original query was in regards to how to count a number of cells that contained "DL", but I also wanted to filter the data is it is going to be analyised in months. The resolution looks as follows:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(G2,ROW($G$3:$G31)-ROW($G$2),,1)),--($G$3:$G31="DL"))
I then created a forumla that counts the number of Pass cells for DL, which looks as follows:
=SUMPRODUCT((G3:G31="DL")*(H3:H31="Pass"))
My issue is that when I filter out the data, the results of my new formila don't change to reflect the data that is shown. I need to add a "SUBTOTAL" to the formula, similar to my first, but I am stumped as to how.
Any thoughts?
