IF Function Help

[AmyYang]

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 

[#2 · Accepted 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])

[#3]

@AmyYang 

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

[#4]

Dear Riny,

Understood, thanks so much for your explanation!

Regards,
Amy

[#5]

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