IF function with an array

I have a worksheet that looks like this: file attached at end of post:

worksheet screen shot.jpg

here is the file itself:

This is what the names are for my ranges

range names.jpg

A14 is a drop list of names that looks like this:

Charles
Amanda
Becky
Polly
Sloan
Anna
Susie


B14 is a drop list of names that looks like this:

Fred
Mike
Lynn
John
James
Polly


F14 is the following array: {=IF(C5:C11=A14,COUNTIF(INDIRECT((CONCATENATE(A14,"_calls"))),"="&B14),0)}

It returns the correct value only if A14 is Charles

HOWEVER, if I choose Amanda from the drop list in A14 and leave B14 as Fred, it returns 0 when it should be 1.

I would appreciate any help in getting this to work correctly.
Thanks,

Welcome to the Forum Parrot61!

Please explain what the "correct value" is--what do you want this formula to do?

An image attachment has limited value. Please attach the Excel file. It's easier than taking a screenshot first and then attaching that. Having your file allows us to see and experiment with your data, layout, formulas, code, and possibly attach a file with a completed solution.

The paper clip icon does not work for attachments. To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

INDIRECT(...) itself drive to name in A14, so just:

Please Login or Register  to view this content.

By the way, I wonder how you control a long list name. Belown formula will solve with no userdefine name:

Please Login or Register  to view this content.