Formula too long for macro?

[BuddJ]

Hia,

I have the following formula, which works when entered into a cell directly, but when I attempt to include it in a macro the text turns red & is not accepted. Unfortunately there is no error message displayed so I don't even have a starter to hit google with, so I'm assuming that at 1021 characters it's possibly too long for a macro ??

The cell formula is :-

=SUM(((((COUNTIFS('Staff List'!$B:$B,$A3,'Staff List'!AI:AI,""Match*""))+(<wbr>COUNTIFS('Staff List'!$B:$B,$A3,'Staff List'!AI:AI,""Expiring*""))+(COUNTIFS('Staff List'!$B:$B,$A3,'Staff List'!AJ:AJ,""Match*""))+(COUNTIFS('Staff List'!$B:$B,$A3,'Staff List'!AJ:AJ,""Expiring*""))+(COUNTIFS('Staff List'!$B:$B,$A3,'Staff List'!AK:AK,""Match*""))+(COUNTIFS('Staff List'!$B:$B,$A3,'Staff List'!AK:AK,""Expiring*""))))/(((COUNTIFS('Staff List'!$B:$B,$A3,'Staff List'!AI:AI,""Match*""))+(COUNTIFS('Staff List'!$B:$B,$A3,'Staff List'!AI:AI,""Expiring*"")+(COUNTIFS('Staff List'!$B:$B,$A3,'Staff List'!AI:AI,""Required*""))+(COUNTIFS('Staff List'!$B:$B,$A3,'Staff List'!AJ:AJ,""Match*""))+(COUNTIFS('Staff List'!$B:$B,$A3,'Staff List'!AJ:AJ,""Expiring*"")+(COUNTIFS('Staff List'!$B:$B,$A3,'Staff List'!AJ:AJ,""Required*""))+(COUNTIFS('Staff List'!$B:$B,$A3,'Staff List'!AK:AK,""Match*""))+(COUNTIFS('Staff List'!$B:$B,$A3,'Staff List'!AK:AK,""Expiring*"")+(COUNTIFS('Staff List'!$B:$B,$A3,'Staff List'!AK:AK,""Required*""))))))))*100)
<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>

The idea behind the formula is :-
- There are 3 columns of data on the worksheet "Staff List" with the calculation result appearing on another worksheet hence the sheet reference.
- Each column contains text data in the cells with the form of "Required", "Expiring", "Match","Not Required"
- I need to count the number of cells containing "Expiring" & "Match" across all three columns
- I then need to divide the above total with a count, also across all 3 columns, of the cells containing "Required", "Expiring" & "Match"
- Multiply by 100 to get a % value

Is there perhaps a more elegant way to achieve the same result ??

Many thanks for any assistance, tips or pointers.

 

[kbrummert]

If you're going to go with a vba solution, I would go with a custom function. Within it, for example, you could have separate variables for each of the countifs on separate lines, then combine them to produce your final result. This would be much easier to see and to troubleshoot.