Formula to check a service isn't selected more than once

[The_Snook]

Morning all,

I have a number of different buildings and three different service types that are available. Each building can only receive one service. I'd like a formula to check that no building has more than one service requested against it.

Example attached.

Thanks in advance,

Snook

[AliGW]

How would it even be possible for any one building to select a service more than once? Your grid allows Yes or nothing, so what would you expect your pink box to say???

Is this what you want?

=COUNTA(C3:C5)>1

[samba_ravi]

or can try
=IF(COUNTIF(C3:C5,"Yes")>1,"Multi","")

[The_Snook]

Hi Ali,

The formula (pink cell) needs to check that each building (across the whole range) doesn't have a "Yes" count of more than 1. If it does I'd like the pink cell to show an output like 'Error'. I'm assuming I'll need an array formula?

Does that make sense?

Regards,

Snook

[AliGW]

OK - you still haven't said what you want it to return, though!

EDIT: That was a quick edit!!!

[samba_ravi]

try
=SUM((MMULT({1,1,1},(C3:L5="Yes")+0)=1)+0)
or
=SUMPRODUCT(((C3:L3="Yes")+(C4:L4="Yes")+(C5:L5="Yes")=1)+0)

[The_Snook]

If it does I'd like the pink cell to show an output like 'Error'.

Cheers samba_ravi, that returns the number of compliant buildings. Can we get it to compare against the total number of buildings and generate a OK/ERROR output?

[AliGW]

Just amend it thus:

=IF(SUM((MMULT({1,1,1},(C3:L5="Yes")+0)=1)+0) < COUNTA(C2:L2),"Error","OK")

[The_Snook]

Magical, cheers both! Apologies for the Monday morning head masher!

[AliGW]

Hardly, and would have been faster with an unambiguous opening post.