How to shorten COUNTIF formulas (without using VBA)

[janmorris]

I have a spreadsheet that i would like to shorten the formulas (if possible).

I have discovered that it is possible to shorten SUMIFS using the {curly brackets} to make an array of the options for a given range.

However, I still havent found if there is similar method for COUNTIF.

I did try to use the {curly brackets} method on a COUNTIF formula, but it doesnt seem to work.

Does anyone know of methods to shorten formulas that have repeated sections, and for what functions they can be applied to?

[AliGW]

If you had MS365, you could use the new LET function, but it seems you have Excel 2016 - is this right? Please update your profile to make this clear.

There are instructions at the top of the page explaining how to attach your sample workbook.

[janmorris]

yes i have Office 16 on Mac.

I just discovered (trying from my thoughts) how to shorten COUNTIF with the curly brackets, the trick is to also wrap the COUNTIF in SUM, so it would look like this:

SUM(COUNTIF(range{variable1,variable2,variable3,variable,4}))

[AliGW]

Please update your profile to say Office 16 for Mac instead of just 16.

You missed a comma:

=SUM(COUNTIF(range,{variable1,variable2,variable3,variable,4}))

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[janmorris]

oh yes indeed, thanks for the correction, and the advice for using the forum, it has been many years since ive used them, time to relearn those skills

[CARACALLA]

Attach your sample workbook.