countifs

[jtodd]

Hi Simple one I am sure -
Can anyone tell me why this formula results in #Value

=COUNTIFS(H17,"=DHL",M17:S17,"=*R*")
H I M N O P Q R S

<colgroup><col style="mso-width-source:userset;mso-width-alt:3913; width:80pt" span="2" width="107"> <col style="mso-width-source:userset;mso-width-alt:2450;width:50pt" width="67"> <col style="mso-width-source:userset;mso-width-alt:2157;width:44pt" width="59"> <col style="mso-width-source:userset;mso-width-alt:1316; width:27pt" span="6" width="36"> </colgroup><tbody>
[TD="class: xl65, width: 107"]KNOWHOW CSC[/TD]
[TD="class: xl65, width: 107"]KNOWHOW CSC[/TD]
[TD="class: xl66, width: 67"] [/TD]
[TD="class: xl67, width: 59"] [/TD]
[TD="class: xl67, width: 36"] [/TD]
[TD="class: xl67, width: 36"] [/TD]
[TD="class: xl67, width: 36"]RS[/TD]
[TD="class: xl67, width: 36"] [/TD]
[TD="class: xl67, width: 36"] [/TD]
[TD="class: xl67, width: 36"] [/TD]

[TD="class: xl65"]DHL[/TD]
[TD="class: xl65"]DHL[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"]RS[/TD]
[TD="class: xl67"]RSC[/TD]
[TD="class: xl67"]RS[/TD]
[TD="class: xl67"]RS[/TD]
[TD="class: xl67"]RSC[/TD]
[TD="class: xl67"]RS[/TD]

[TD="class: xl65"]KNOWHOW CSC[/TD]
[TD="class: xl65"]KNOWHOW CSC[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"]RS[/TD]
[TD="class: xl67"]RS[/TD]
[TD="class: xl67"]RS[/TD]
[TD="class: xl67"]RS[/TD]
[TD="class: xl67"]RS[/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl65"]KNOWHOW CSC[/TD]
[TD="class: xl65"]KNOWHOW CSC[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"]RS[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"]RS[/TD]
[TD="class: xl67"]RS[/TD]
[TD="class: xl67"]RS[/TD]
[TD="class: xl67"]RS[/TD]

[TD="class: xl65"]KNOWHOW CSC[/TD]
[TD="class: xl65"]KNOWHOW CSC[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"]RS[/TD]
[TD="class: xl67"]S[/TD]
[TD="class: xl67"]S[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"]RS[/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl65"]DHL[/TD]
[TD="class: xl65"]DHL[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"]RS[/TD]
[TD="class: xl67"]S[/TD]
[TD="class: xl67"]RSC[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"]RS[/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl65"]DHL[/TD]
[TD="class: xl65"]DHL[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"]RSC[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"]RS[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"]RS[/TD]
[TD="class: xl67"] [/TD]

</tbody>

Any help

 

[FormR]

Can anyone tell me why this formula results in #Value

Hi, because COUNTIFS() requires all the ranges to be the same size.

Maybe you wanted..

=IF(H17="DHL",COUNTIFS(M17:S17,"*R*"),0)

 

[jtodd]

Many thanks , I did not realise that about COUNTIFS()

Hi, because COUNTIFS() requires all the ranges to be the same size.

Maybe you wanted..

=IF(H17="DHL",COUNTIFS(M17:S17,"*R*"),0)