Avatar of Berry Metzger
Berry Metzger
Flag for United States of America

asked on 

How to sumproduct using subtotal of visible rows with criteria

I am using this formula to return the product of two columns fonly or visible [filtered] rows.  Now I would like to include the criteria, J7:J121="X", to  =SUMPRODUCT(SUBTOTAL(9,OFFSET($C7:$C121,ROW($C7:$C121)-MIN(ROW($C7:$C121)),0,1)),$I7:$I121)... so that the formula selects from visible rows only rows with an "X" in column J.  I plan to use this formula as the divisor in column headings to calculate percent completion of each columns of data based on the new formula that uses the added criteria, J7:J121="X" to further exclude rows without an "X" in column J [or K, L, M...]
Thank you in advance
Microsoft Excel

Avatar of undefined
Last Comment
Berry Metzger

8/22/2022 - Mon