I have something i'm fairly stuck on in excel that i'm wondering if you know the cure for.
I have an original sheet with all of the con-evb data in it. I used that with a COUNTIF function on another tab to calculate a time series (Date of Approval for example) to take a week at a time and count how many jobs were approved between the Monday of that week and the following Sunday (including those dates). That all works great.
What I can't get is when I filter the original sheet by installer, or state, or however to filter it, the COUNTIF still counts all the data, even the hidden rows/filtered rows.
Now I was able to subtotal for regular counts, averages, sum, etc to take measurements of the filtered data, but I can't get the SUBTOTAL to account for filtered data between date ranges, thats where I'm stuck. I thought I was getting close with the SUMPRODUCT function but the need for counts between desired date ranges is messing up my equation. Most examples I see online are for people wanting to use countifs to see how many pears are the fruit of choice in a certain city of the country if you filter for just 15 lines of info that Jerry reviewed. I'm looking for something more specific to include date ranges that are then going to be filtered by company name. i know i could use a pivot table but thats not quite getting me what I want.
I have an original sheet with all of the con-evb data in it. I used that with a COUNTIF function on another tab to calculate a time series (Date of Approval for example) to take a week at a time and count how many jobs were approved between the Monday of that week and the following Sunday (including those dates). That all works great.
What I can't get is when I filter the original sheet by installer, or state, or however to filter it, the COUNTIF still counts all the data, even the hidden rows/filtered rows.
Now I was able to subtotal for regular counts, averages, sum, etc to take measurements of the filtered data, but I can't get the SUBTOTAL to account for filtered data between date ranges, thats where I'm stuck. I thought I was getting close with the SUMPRODUCT function but the need for counts between desired date ranges is messing up my equation. Most examples I see online are for people wanting to use countifs to see how many pears are the fruit of choice in a certain city of the country if you filter for just 15 lines of info that Jerry reviewed. I'm looking for something more specific to include date ranges that are then going to be filtered by company name. i know i could use a pivot table but thats not quite getting me what I want.
