May 08 2022 11:47 PM
Hi,
I am trying to use the IF function but the output says "#SPILL".
May you help me let me know where I went wrong with the IF Function equation?
Kindly see attached for "Raw Data" and "Summary".
Thanks,
Amy
May 09 2022 12:04 AM - edited May 09 2022 12:09 AM
Solution@AmyYang When you take your formula outside the table, you'll note that it creates a dynamic array.
And you can't use that inside a structured table. You could wrap the formula in SUM to return a single value.
Alternatively use SUMIFS
=SUMIFS(Table1[ASR (W)
(Incidence per 100 000 Population)],Table1[Indication],[@Indication],Table1[Population],[@Country])
May 09 2022 01:50 AM
As @Riny_van_Eekelen has pointed out, each row of your summary table examines every row of your raw date. In the event of more than one match, what would you wish to have returned: the first, the last, the sum, the max? If you can be absolutely sure there is only one match, pretty much any one of these would do.
The simplest answer is then to wrap your existing formula in SUM, or you could use an XLOOKUP
= XLOOKUP(1,
(Table1[Indication]=[@Indication]) * (Table1[Population]=[@Country]),
Table1[ASR (W)])
May 09 2022 10:49 PM
May 09 2022 10:52 PM
May 09 2022 12:04 AM - edited May 09 2022 12:09 AM
Solution@AmyYang When you take your formula outside the table, you'll note that it creates a dynamic array.
And you can't use that inside a structured table. You could wrap the formula in SUM to return a single value.
Alternatively use SUMIFS
=SUMIFS(Table1[ASR (W)
(Incidence per 100 000 Population)],Table1[Indication],[@Indication],Table1[Population],[@Country])