SUMPRODUCT help

[lbarraclough]

Hi

I'm trying to get a SUMPRODUCT to work. I have managed to get it working using the formula below (both with or without named ranges for the COURSES!K:W):

=SUMPRODUCT(COUNTIF($D$3,COURSES!E:E)*($D$4<=COURSES!K:W)*($D$5>=COURSES!K:W))

But when I try and change the cell range (formula below) it falls over and tells me there is either a spill error or a REF error. I've tried changing to a named range at that still won't work. Really struggling!

=SUMPRODUCT(COUNTIF($D$3,COURSES!E:E)*($D$4<=COURSES!X:AJ)*($D$5>=COURSES!X:AJ))

RAW dataFormula

[#2]

Oh, I think I see what you're trying to do now. Try this:

=SUMPRODUCT((COURSES!E:E=$D$3)*($D$4<=COURSES!X:AJ)*($D$5>=COURSES!X:AJ))

But, the calculation is going to lag a little using entire column references. If possible, I would suggest narrowing the range or using a structured table (structured table references will automatically update when you expand the size of the table).

[#3]

@JMB17 

Tried that formula and still no luck

File can be found at https://1drv.ms/x/s!AlBXijCQ6SGI5hwK1H7ASOrMeC7M?e=xzcvNP

[#4]

I can't do anything in that file (edit or save to my machine). Can you upload it here (click on reply, not quick reply, and you should see an option below the comment box to upload a file).

[#5]

@lbarraclough 

At least two issues

1) Within second range you have #REF! error, thus result of calculation returns the same error

2) With named ranges all range in formulas shall be of the same size, when it works (if ignore above error). Named range are corrected in attached file.