SUMIFS formulea results $0.00

Hi

I found you on a google search, I then searched the forum for an answer but I don't think anything specifically helps me.

I have a workbook with 5 sheets, I have four sheets for separate 'brokers' that get referrals from a number of people. I have set the referrer up as a drop down box, and three other columns with yes/no drop downs. I am hoping this is my problem as I cant see any syntax errors in my formula.

I am trying to count the $ value of referrals based on if it is settled, NPW or in progress

Can I get some assistance with why this isnt working please.

Also, maybe this is a clumsy way to achieve what i want, any direction on a better method would be great!

Thank you

GMC

I put sheet name at K3:K6

B4
=SUMPRODUCT(COUNTIFS(INDIRECT($K$3:$K$6&"!A:A"),A4))

C4:E4
=SUMPRODUCT(COUNTIFS(INDIRECT($K$3:$K$6&"!A:A"),$A4,INDIRECT($K$3:$K$6&"!C"&COLUMN(D4)&":C"&COLUMN(D4),),"Yes "))

F4
=SUMPRODUCT(SUMIFS(INDIRECT($K$3:$K$6&"!C:C"),INDIRECT($K$3:$K$6&"!A:A"),$A4))

Copy all down

No need to google them, if you dont understand something, ask for an explanation