SUMPRODUCT #SPILL! Error

[SaraWitch]

Hello peeps,

I am trying to sum a column of figures in another workbook between two dates and based on text in two other different columns (but still in the other workbook). The closest formula I've got to is giving a #SPILL! error and I can't figure out how to fix it. The formula is:

=SUMPRODUCT('[Workbook A]'Sheet 1'!$BK:$BK)*('[Workbook A]'Sheet 1'!$BD:$BD<=H$2)*('[Workbook A]'Sheet 1'!$BD:$BD>=C$2)*'[Workbook A]'Sheet 1'!$F:$F="EBC")*('[Workbook A]'Sheet 1'!$H:$H="General Needs")

BK is the figure I want to sum from other workbook.
BD is the date I want to select from other workbook.
H is the finish date in current workbook.
C is the start date in current workbook.
F is the first text match in other workbook
H is the second text match in other workbook.

I hope this makes sense for someone to know what I'm doing wrong! Of course, let me know if clarification is needed...

Ta muchly, folks!

 

[Fluff]

You are missing some brackets, try

=SUMPRODUCT(('[Workbook A]Sheet 1'!$BK:$BK)*('[Workbook A]Sheet 1'!$BD:$BD<=H$2)*('[Workbook A]Sheet 1'!$BD:$BD>=C$2)*('[Workbook A]Sheet 1'!$F:$F="EBC")*('[Workbook A]Sheet 1'!$H:$H="General Needs"))

Although you should avoid using whole column references with a formula like that.

 

[SaraWitch]

Thank you @Fluff! Only thing is, I'm getting #VALUE! now...? I added row range in case the whole column formula was the cause, but still getting error.

 

[Fluff]

Do you have any text values in column BK including any formulae that return ""

 

[SaraWitch]

I have a formula in BK that looks at two other columns:

=IF(ISERROR(($X34/7)*$BI34),"",($X34/7)*$BI34)

 

[Fluff]

That is the problem "" is text. Does this need to work in 2010, or just 365?

 

[SaraWitch]

Probably just 365 (I think most users have now been transitioned!).

 

[Fluff]

Ok, try this

=SUM(FILTER('[Workbook A]Sheet 1'!$BK:$BK,('[Workbook A]Sheet 1'!$BD:$BD<=H$2)*('[Workbook A]Sheet 1'!$BD:$BD>=C$2)*('[Workbook A]Sheet 1'!$F:$F="EBC")*('[Workbook A]Sheet 1'!$H:$H="General Needs")))

 

[SaraWitch]

I am in awe! Amazing, as always!! Thank you soooo much, @Fluff!

 

[Fluff]

Glad to help & thanks for the feedback.