View Full Version : Is there a cleaner way to write this formula?
austenr
04-20-2018, 12:38 PM
=SUM(COUNTIFS($G$4:$O$4,{"Chargeback","pending response","Pending Other","High","Med","X"}))+SUM(COUNTIFS($B$4:$E$4,{"Chargeback","pending response","Pending Other","High","Med","X"}))
p45cal
04-21-2018, 05:40 AM
If you're certain that column F (or specifically cell F4) can never contain one of those things you're counting then:
=SUM(COUNTIFS($B$4:$O$4,{"Chargeback","pending response","Pending Other","High","Med","X"}))
Separately, you could also refer to a named range containing those strings (Chargeback etc.) but you'd have to commit the formula to the sheet using Ctrl+Shift+Enter, not just Enter:
=SUM(COUNTIFS($G$4:$O$4,Stuff))+SUM(COUNTIFS($B$4:$E$4,Stuff))
austenr
04-21-2018, 06:27 AM
thanks
Aflatoon
04-23-2018, 01:48 AM
Shorter than the original, but less readable IMO:
=SUMPRODUCT(($B$4:$O$4={"Chargeback";"pending response";"Pending Other";"High";"Med";"X"})*(COLUMN($B$4:$O$4)<>6))
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.