Dec 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))
Dec 17 2020 11:04 PM
Dec 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
Dec 18 2020 07:17 AM
Dec 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.