=SUMPRODUCT(COUNTIF(INDIRECT("'"&A25:A31&"'!L7:L30"),"<60"))
This is the formula I'm working with. I want to replace the 60 with .60*F3, but of course =SUMPRODUCT(COUNTIF(INDIRECT("'"&A25:A31&"'!L7:L30"),"<(.60*F3)")) does not work. Do I need to insert a new INDIRECT parameter? and, if so, where? What would the new formula be?
Additional Info: F3 contains a number that is manually input.
Thanks for any help.
Last edited by pandakor; 05-06-2020 at 08:33 AM.
Try this
=SUMPRODUCT(COUNTIF(INDIRECT("'"&A25:A31&"'!L7:L30"),"<"&60*F3))
This may be working. I have to plug it into a few more equations and I'll let you know soon. Thanks.Originally Posted by Phuocam
Wow. amazing. I"m an excel amateur, just self taught off the internet, and I struggled with that for hours. Thank you so much. It works perfectly across all my equations.
I have one last question, and it may not even be possible, but is there a way to simplify this equation? However, it does work just fine for my purposes. It just looks messy and I'd like to learn a new way to do it.
=SUMPRODUCT(COUNTIF(INDIRECT("'"&A25:A31&"'!L7:L30"),">="&0.6*F3)) - SUMPRODUCT(COUNTIF(INDIRECT("'"&A25:A31&"'!L7:L30"),">="&0.65*F3))
So, if F3 is 100, this formula would give me the total number of cells with a value of between 60 and 65 in L7:30 across multiple sheets.
Try
=SUMPRODUCT(COUNTIFS(INDIRECT("'"&A25:A31&"'!L7:L30"),">="&0.6*F3,INDIRECT("'"&A25:A31&"'!L7:L30"),"<="&0.65*F3))
I had to modify it ever so slightly for my purposes, as I didn't want it to include values of 65, so I just removed the equal to sign:
=SUMPRODUCT(COUNTIFS(INDIRECT("'"&A25:A31&"'!L7:L30"),">="&0.6*F3,INDIRECT("'"&A25:A31&"'!L7:L30"),"<"&0.65*F3))
Again wow. And so quick on the responses. I can't thank you enough kind stranger.
Edit: I also learned about COUNTIFS: a valuable lesson.
Last edited by pandakor; 05-06-2020 at 09:14 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks