SUM COUNTIF Horror

[The Grim Discovery]

Peoples,

Could someone do a good deed an help me manage this disgrace of a formula into something more succinct?
As you'll see it's calculating the frequency of 'yes' against the frequency of 'yes' and 'no' depending on the condition in B5 is met. It's actually part of a much larger formula containing many similar countifs and it's slowing my worksheet down horribly.

IFERROR(SUM(COUNTIFS(Data!F:F,B5,Data!$K:$K,"yes")+COUNTIFS(Data!F:F,B5,Data!$J:$J,"yes")+COUNTIFS(Data!F:F,B5,Data!$N:$N,"yes/SUM(COUNTIFS(Data!F:F,B5,Data!$K:$K,"no")+COUNTIFS(Data!F:F,B5,Data!$J:$J,"no")+COUNTIFS(Data!F:F,B5,Data!$N:$N,"no")+(Data!F:F,B5,Data!$K:$K,"yes")+COUNTIFS(Data!F:F,B5,Data!$J:$J,"yes")+COUNTIFS(Data!F:F,B5,Data!$N:$N,"yes"))," ")

I've spent several hours trying to make use SUMIF and SUMPRODUCT and frankly have got nowhere.

Thanks in advance

 

[Special-K99]

First step is to limit your column ranges, get rid of F:F and replace it with something sensible like F1:F1000 depending on how much data you will have.
Same for other column ranges.

This formula is incorrect, no closing quote or bracket yet another SUM begins ?????
And the other section looks wrong too, should this be a COUNTIF or something else
Also looks like you might need another bracket so you're doing a SUM() / SUM, at the moment youre doing
a SUM(.COUNTIF(.. with a / SUM( COUNTIF..)), doesnt look at all right.

Please correct so we know EXACTLY what the formula is.

IFERROR(SUM(COUNTIFS(Data!F:F,B5,Data!$K:$K,"yes")+COUNTIFS(Data!F:F,B5,Data!$J:$J,"yes")+COUNTIFS(Data!F:F,B5,Data!$N:$N,"yes/SUM(COUNTIFS(Data!F:F,B5,Data!$K:$K,"no")+COUNTIFS(Data!F:F,B5,Data!$J:$J,"no")+COUNTIFS(Data!F:F,B5,Data!$N:$N,"no")+(Data!F:F,B5,Data!$K:$K,"yes")+COUNTIFS(Data!F:F,B5,Data!$J:$J,"yes")+COUNTIFS(Data!F:F,B5,Data!$N:$N,"yes"))," ")

 

[SpillerBD]

SUMIF or SUMPRODUCT are the "old" ways of doing PIvot Table analysis. Yes sometimes a regular Pivot Table can't do what you need... but Power Pivot can!
SUMPRODUCT is often used in an ARRAY (CSE) version which may be another complication for the execution of your formula.

The semi-brutish way would be VBA to do the calculation you need and past the value. It loads the workbook down when running the macro, but once calculated any subsequent CALCULATE Worksheet/Workbook won't have that formula to trudge thru.