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!

 

[SaraWitch]

Hey @Fluff.

Ach. I am now copying this formula to another cell with slightly different criteria, but am getting a #CALC error, which I don't know how to fix!

So, the formula you gave yesterday worked in cell C8. When I copy this to D8 and change the search words, it shows the error, and so on for the six other cells I need this formula for. Revised formula I used in D8 is:

=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="Retirement Housing")))

E8:

=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="EHICL")*('[Workbook A]Sheet 1'!$H:$H="PSL (EHICL)")))

And so on...

Any further help would be gratefully received!

 

[Fluff]

The #calc error means there was nothing that matches all criteria, you can get rid of it like

=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="Retirement Housing"),0))

 

[SaraWitch]

Thank you so much @Fluff - worked a treat!

 

[Fluff]

My pleasure.