Combined matrix sum countif calculation

[RIC63]

HI
I have the formula {=SUM(COUNTIF(BA69:BC83;$A$2:$F$2))} which counts how many values in $A$2:$F$2 are found in an area (BA69:BC83) ..and it works

I should modify it so that the number of occurrences found is returned and if there are none, instead of returning 0 (zero) it returns the letter 'x'... I tried to modify it :

=IF(SUM(COUNTIF(BA69:BC83,$A$2:$F$2))>0;"=(SUM(COUNTIF(BA69:BC83,$A$2:$F$2))";" x")

...without success,

any suggestions to use in excel 2021 ? Thank you

 

[Fluff]

How about

=IFERROR(1/(1/SUM(COUNTIF(BA69:BC83,$A$2:$F$2)));"x")

 

[RIC63]

How about

=IFERROR(1/(1/SUM(COUNTIF(BA69:BC83,$A$2:$F$2)));"x")

Thank you Fluff

I had to adapt it to the syntax and function names of the Italian version

=SE.ERRORE(1/(1/SOMMA(CONTA.SE(AU69:AW83;$A$2:$F$2)));"x")

so use Ctrl+Shift+Enter for input and it works perfectly, thanks again

 

[Fluff]

Glad we could help & thanks for the feedback.