troubleshooting Question

How to sumproduct using subtotal of visible rows with criteria

Avatar of Berry Metzger
Berry MetzgerFlag for United States of America asked on
Microsoft Excel
5 Comments1 Solution805 ViewsLast Modified:
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
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros