I have two worksheets in the same workbook. One (Calc!) shows an analysis of the data in the other (Records!). I am trying to create a chart in Calc! showing the breakdown of the data in Records!. I was initially using a COUNTIFS function, but I needed it to ignore filtered rows.
Ultimately, I need it to return a count of visible rows with a number in "Column A" larger than Calc!$C$7 AND a number between 50 and 100 in "Column B". I have five different ranges to show separate calculations, so that's why I just can't filter them both.
I've used the following for the $C$7 calculation, which works, but when I try to add any calculation for column B in the same formula or separate, it does not work and returns an "N/A#". I'm using...
=SUMPRODUCT(SUBTOTAL(2,OFFSET(Records!$A$2:$A$100000,ROW(Records!$A$2:$A$100000)-ROW(Records!$A$2),0,1)),--(Records!$A$2:$A$100000>=$C$7))
In my amateur Excel world it would be something like:
=SUBTOTAL(Records!$A:$A,>=$C$7)-SUBTOTALS(Records!$B:$B,>50,<=101)
Help!
Bookmarks