SumIFs Function doesn't work!!! a

[BittenApple]

Hello team,
I am using SumIFs Function and I am using named ranges.
Per post on 13 Aug 2020, I have:
SUMIFS(Patients[Amount],Patients[BirthDate],">="&DATE(1970,1,1)) ...


Now I want to do same method I can't do.
My data is one sheet and I want to do the calculation on a different sheet.

What the problem is?

Regards,
BittenApple

[HansV]

The syntax

=SumIFS(tdata[num1], tdata[hospital],$D$31, tdata[carrier],$E$31,tdata[diagcode1],$F$31)

assumes that the range D3:J13 has been converted to a table named tdata, not just a named range.
Unfortunately, since you have already created a named range tdata, you cannot use this name for a table anymore, even if you delete the named range.
So you'll have to use another name.

Another problem is that your table uses "Hospital A" and "Hospital B", but D19, D20 and D31 contain "HospitalA" and "HospitalB".
And you have a diagnosis code in E31 but your SUMIFS formula expects a carrier.

So it's all very confusing.

[BittenApple]

Hello Hans,
Thanks for the response.
Sometimes, the name we created is inactive in a name manager, so we can't delete it.
I think I had this problem before and you showed me how to fix it.
I have to select the range without headers and then I check my data has a header, then that works.
Anyway, I found where the problem was coming from.
Regards,
Bitt

[HansV]

The Name Manager shows two types of names:
- Defined names. You can delete these in the Name Manager
- Tables. You can not delete these in the Name Manager
You can recognize them by their icons - they are slightly different:

S0448.png

[BittenApple]

Thanks!