calculates incorrect percentage

[Godders199]

Hello, i have a long formula, that displays the number and percentage in the same cell, however currently it is calculating the % incorrectly

=IFERROR(COUNTIFS(Appeal!$C:$C,"*FDMA*",Appeal!$D:$D,">="&MI!$E$2,Appeal!$D:$D,"<="&MI!$G$2,Appeal!$H:$H,"suitable")+COUNTIFS(Appeal!$C:$C,"*HSBCMA*",Appeal!$D:$D,">="&MI!$E$2,Appeal!$D:$D,"<="&MI!$G$2,Appeal!$H:$H,"Suitable")+COUNTIFS(Appeal!$C:$C,"*MSBMA*",Appeal!$D:$D,">="&MI!$E$2,Appeal!$D:$D,"<="&MI!$G$2,Appeal!$H:$H,"suitable")+COUNTIFS(Appeal!$C:$C,"*MPM*",Appeal!$D:$D,">="&MI!$E$2,Appeal!$D:$D,"<="&MI!$G$2,Appeal!$H:$H,"suitable")&"("&TEXT((COUNTIFS(Appeal!$C:$C,"*fdma*",Appeal!$D:$D,">="&MI!$E$2,Appeal!$D:$D,"<="&MI!$G$2,Appeal!$H:$H,"suitable")+COUNTIFS(Appeal!$C:$C,"*HSBCMA*",Appeal!$D:$D,">="&MI!$E$2,Appeal!$D:$D,"<="&MI!$G$2,Appeal!$H:$H,"Suitable")+COUNTIFS(Appeal!$C:$C,"*MSBMA*",Appeal!$D:$D,">="&MI!$E$2,Appeal!$D:$D,"<="&MI!$G$2,Appeal!$H:$H,"suitable")+COUNTIFS(Appeal!$C:$C,"*MPM*",Appeal!$D:$D,">="&MI!$E$2,Appeal!$D:$D,"<="&MI!$G$2,Appeal!$H:$H,"suitable")/$E58),"0.0%")&")","-(-)")

I have split the countifs out and the count figure comes to 2 and the count for the percentage is also 2..

The figure in E58 is 3 so 2/3 *100 should equal 66.6, but it comes out at 133.33, have checked the source of the data and the figures are correct, so presume there is something wrong somewhere in the formula.

If anyone can look through it and help me it would be much appreciated, been looking at it for hours .

 

[steve the fish]

Try keeping the countif formulas together in one cell. What do they add up to now? I suspect sometimes you are double counting. Its possible for one cell to be true for more than one of your conditions.

 

[Godders199]

Thanks I have tried spliting the formulas in different cells and I get the correct answer, as my first assumption was i am double counting. but both parts return 2

there are only 3 rows in the data source at the moment
[TABLE="width: 147"]
<tbody>[TR]
[TD]Unsuitable[/TD]
[/TR]
[TR]
[TD]Suitable[/TD]
[/TR]
[TR]
[TD]Suitable[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

when i use the formula to count unsuitable it is correct 1 (33.3%) its just when it is looking for suitable its getting the % wrong.

I would use separate cells for the count and percentage , but this is one row in 50 and it would look wrong... I will look tomorrow, i am sure it is something obvious.

 

[steve the fish]

If there are only 3 rows paste them here. Then tell us what is in MI E2 and G2.

 

[RoryA]

You are only dividing the final COUNTIFS part by E58. You should use:

=IFERROR(COUNTIFS(Appeal!$C:$C,"*FDMA*",Appeal!$D:$D,">="&MI!$E$2,Appeal!$D:$D,"<="&MI!$G$2,Appeal!$H:$H,"suitable")+COUNTIFS(Appeal!$C:$C,"*HSBCMA*",Appeal!$D:$D,">="&MI!$E$2,Appeal!$D:$D,"<="&MI!$G$2,Appeal!$H:$H,"Suitable")+COUNTIFS(Appeal!$C:$C,"*MSBMA*",Appeal!$D:$D,">="&MI!$E$2,Appeal!$D:$D,"<="&MI!$G$2,Appeal!$H:$H,"suitable")+COUNTIFS(Appeal!$C:$C,"*MPM*",Appeal!$D:$D,">="&MI!$E$2,Appeal!$D:$D,"<="&MI!$G$2,Appeal!$H:$H,"suitable")&"("&TEXT((COUNTIFS(Appeal!$C:$C,"*fdma*",Appeal!$D:$D,">="&MI!$E$2,Appeal!$D:$D,"<="&MI!$G$2,Appeal!$H:$H,"suitable")+COUNTIFS(Appeal!$C:$C,"*HSBCMA*",Appeal!$D:$D,">="&MI!$E$2,Appeal!$D:$D,"<="&MI!$G$2,Appeal!$H:$H,"Suitable")+COUNTIFS(Appeal!$C:$C,"*MSBMA*",Appeal!$D:$D,">="&MI!$E$2,Appeal!$D:$D,"<="&MI!$G$2,Appeal!$H:$H,"suitable")+COUNTIFS(Appeal!$C:$C,"*MPM*",Appeal!$D:$D,">="&MI!$E$2,Appeal!$D:$D,"<="&MI!$G$2,Appeal!$H:$H,"suitable"))/$E58,"0.0%")&")","-(-)")

or more simply:

=IFERROR(SUM(COUNTIFS(Appeal!$C:$C,{"*FDMA*","*HSBCMA*","*MSBMA*","*MPM*"},Appeal!$D:$D,">="&MI!$E$2,Appeal!$D:$D,"<="&MI!$G$2,Appeal!$H:$H,"suitable"))&"("&TEXT(SUM((COUNTIFS(Appeal!$C:$C,{"*fdma*","*HSBCMA*","*MSBMA*","*MPM*"},Appeal!$D:$D,">="&MI!$E$2,Appeal!$D:$D,"<="&MI!$G$2,Appeal!$H:$H,"suitable"))/$E58,"0.0%")&")","-(-)")

 

[Godders199]

Hi Steve data source is I can only paste in from column c onwards

[TABLE="width: 696"]
<tbody>[TR]
[TD]MPM-FC[/TD]
[TD="align: right"]05/04/2018[/TD]
[TD]IIP[/TD]
[TD]Item[/TD]
[TD]xxx[/TD]
[TD]Unsuitable[/TD]
[/TR]
[TR]
[TD]MPM-FC[/TD]
[TD="align: right"]05/04/2018[/TD]
[TD]IIP[/TD]
[TD]Item[/TD]
[TD]xxxx[/TD]
[TD]Suitable[/TD]
[/TR]
[TR]
[TD]FDMA[/TD]
[TD="align: right"]04/04/2018[/TD]
[TD]IIP[/TD]
[TD]Item[/TD]
[TD]xxxx[/TD]
[TD]Suitable[/TD]
[/TR]
</tbody><colgroup><col><col><col span="2"><col><col></colgroup>[/TABLE]

E2 and G2 are just the start and end date of the months

[TABLE="width: 380"]
<tbody>[TR]
[TD]From[/TD]
[TD="align: right"]01/04/2018[/TD]
[TD]To[/TD]
[TD="align: right"]30/04/2018[/TD]
[/TR]
</tbody><colgroup><col><col><col><col></colgroup>[/TABLE]

 

[Godders199]

thanks , can see you have added a ) to my formula , could not see where you added the ( but it works .

Second more simpler formula errors saying "your formula is missing a parenthesis , and highlights the final "

be good if i can get this to work as a lot simpler , my formula was giving me a headache..

 

[RoryA]

Sorry - that's what I get for typing freehand without testing. It should be:

=IFERROR(SUM(COUNTIFS(Appeal!$C:$C,{"*FDMA*","*HSBCMA*","*MSBMA*","*MPM*"},Appeal!$D:$D,">="&MI!$E$2,Appeal!$D:$D,"<="&MI!$G$2,Appeal!$H:$H,"suitable"))&"("&TEXT(SUM(COUNTIFS(Appeal!$C:$C,{"*fdma*","*HSBCMA*","*MSBMA*","*MPM*"},Appeal!$D:$D,">="&MI!$E$2,Appeal!$D:$D,"<="&MI!$G$2,Appeal!$H:$H,"suitable"))/$E58,"0.0%")&")","-(-)")

 

[Godders199]

Thanks Rory, that works .

Learnt so much using this site. just need to get better at working out these simpler solutions.

 

[RoryA]

Glad to help.