Sumproduct help needed

lbarraclough

New Member
Joined
Sep 29, 2020
Messages
4
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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))
 

Attachments

  • 67B9150E-F269-48C8-AE3E-0D46821B538C.jpeg
    67B9150E-F269-48C8-AE3E-0D46821B538C.jpeg
    123.8 KB · Views: 2
  • EA192869-E4E3-4941-84F3-737C04A249F2.jpeg
    EA192869-E4E3-4941-84F3-737C04A249F2.jpeg
    32.5 KB · Views: 2
  • FEBE70B3-8D74-492C-A7D2-7D90DCC17723.jpeg
    FEBE70B3-8D74-492C-A7D2-7D90DCC17723.jpeg
    32 KB · Views: 2

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

KevCarter

Board Regular
Joined
Dec 7, 2013
Messages
145
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

Board Regular
Joined
Dec 7, 2013
Messages
145
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Sep 29, 2020
Messages
4
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,898
Office Version
  1. 365
Platform
  1. Windows
There are two problems with this formula
Excel 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

Well-known Member
Joined
Feb 15, 2002
Messages
4,563
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
ABCDEF
1Name of Subject:Literacy E2With named ranges
2Start Date:1-Nov-20Packs Sent:11
3End Date:30-Nov-20Packs Returned:11
Summary
Cell Formulas
RangeFormula
F2F2=SUMPRODUCT((course=$B$1)*(sentout>=$B$2)*(sentout<=$B$3))
F3F3=SUMPRODUCT((course=$B$1)*(back>=$B$2)*(back<=$B$3))
Named Ranges
NameRefers ToCells
back=COURSES!$X$2:$AJ$900F3
course=COURSES!$E$2:$E$900F2:F3
sentout=COURSES!$K$2:$W$900F2
Cells with Data Validation
CellAllowCriteria
B1List=$J$2:$J$17
 

Watch MrExcel Video

Forum statistics

Threads
1,122,455
Messages
5,596,231
Members
414,047
Latest member
debbos

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top