Sumproduct help needed

[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))

 

[KevCarter]

I don't have data to test it, but please try another set of parenthesis around your first argument and I'll cross my fingers for you.

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

 

[KevCarter]

Oops, I think you also have your Countif arguments backwards.

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

 

[lbarraclough]

Oops, I think you also have your Countif arguments backwards.

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

Hi tried both of those and unfortunately it works for one set of data but not for the other...
I've uploaded the file to my OneDrive and just hope there is an answer out there

help.xlsx

Microsoft Excel Workbook

1drv.ms

 

[Fluff]

There are two problems with this formula

=SUMPRODUCT((courses=$B$1)*($B$2<=sentout)*($B$3>=sentout))

One is that you do not have a range named "courses" it's "course"
The other is that the named ranges cover a different number of rows. The number of rows should be the same for both ranges

 

[Dave Patton]

Formulas do not require Array Enter CSE
Remove Ref errors in data
Consistent Range Names
N.B. check to ensure that the results are correct

help.xlsx
	A	B	C	D	E	F
1	Name of Subject:	Literacy E2				With named ranges
2	Start Date:	1-Nov-20		Packs Sent:		11
3	End Date:	30-Nov-20		Packs Returned:		11
Summary

Cell Formulas
Range	Formula
F2	F2	=SUMPRODUCT((course=$B$1)*(sentout>=$B$2)*(sentout<=$B$3))
F3	F3	=SUMPRODUCT((course=$B$1)*(back>=$B$2)*(back<=$B$3))

Named Ranges
Name	Refers To	Cells
back	=COURSES!$X$2:$AJ$900	F3
course	=COURSES!$E$2:$E$900	F2:F3
sentout	=COURSES!$K$2:$W$900	F2

Cells with Data Validation
Cell	Allow	Criteria
B1	List	=$J$2:$J$17