Does this only work with Numeric answers? The answers are both.

"Dave" wrote:

Hi,
I think you say that of the multiple "Hospital" entries in Column A, only
one of them will have an entry in Column B.
If this is so, try:
=SUMPRODUCT(--(A3:A100=A1),(B3:B100))
You would have to insert the name of the other sheet (eg 'Sheet(1)!'A3:A100)
Regards - Dave