Forum Discussion
IF Function Help
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
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])
4 Replies
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)])- Dear Peter,
Noted, thank you for your detailed explanation, that's helpful.
Thank you for sharing the formula with SUM versus XLOOKUP for comparison.
Regards,
Amy
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])- Dear Riny,
Understood, thanks so much for your explanation!
Regards,
Amy
