PDA

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))