 SOLVED

# 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

4 Replies
best response confirmed by AmyYang (Contributor)
Solution

# Re: IF Function Help

@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])``````

# Re: IF Function Help

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)])``````

# Re: IF Function Help

Dear Riny,

Understood, thanks so much for your explanation!

Regards,
Amy

Dear Peter,