12-17-2020 03:18 PM
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 data
Formula
12-17-2020 11:04 PM
12-18-2020 01:26 AM
Tried that formula and still no luck
File can be found at https://1drv.ms/x/s!AlBXijCQ6SGI5hwK1H7ASOrMeC7M?e=xzcvNP
12-18-2020 07:17 AM
12-19-2020 02:38 AM
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.