# sumproduct and division

#### evanslee

##### New Member
Hi,

This is my first post, and only as a final resort.

Im sure someone must know the answer to this, but...

Code:
``=SUMPRODUCT(--('Sheet1'!\$K\$2:\$K\$3223>=\$B5),--('Sheet1'!\$K\$2:\$K\$3223<\$B6),--('Sheet1'!\$CM\$2:\$CM\$3223=C\$4))/384``
How can i make the above work by making the last '384' dynamic, the above works but i need it to do this...
Code:
``=SUMPRODUCT(--('Sheet1'!\$K\$2:\$K\$3223>=\$B5),--('Sheet1'!\$K\$2:\$K\$3223<\$B6),--('Sheet1'!\$CM\$2:\$CM\$3223=C\$4))/J5``
but sadly that last version does not work... any ideas guys?
thanks

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Hi, welcome to the board!

What didn't work exactly?
What result did you get? What result did you expect?
What's in J5?

Thanks for the really quick reply.

In J5 is the number 384
So im trying to calculate

here is another way of displaying what im trying to do, 1st one does not work, I need a way to ensure it sees the J5 cell reference and divides the SUMPRODUCT value by it.

Code:
``=SUM(SUMPRODUCT(--('Sheet1'!\$K\$2:\$K\$3223>=\$B5),--('Sheet1'!\$K\$2:\$K\$3223<\$B6),--('Sheet1'!\$CM\$2:\$CM\$3223=C\$4))/J5)``
but if i take out the cell reference and put in the number 384 instead, it then works out the sum correctly.

Code:
``=SUM(SUMPRODUCT(--('Sheet1'!\$K\$2:\$K\$3223>=\$B5),--('Sheet1'!\$K\$2:\$K\$3223<\$B6),--('Sheet1'!\$CM\$2:\$CM\$3223=C\$4))/384)``

Regards,
Lee[/code]

well syntactically the formula's OK, so something else must be going on.

1) the sum() bit is redundant:

SUMPRODUCT(--(Sheet1!\$K\$2:\$K\$3223>=\$B5),--(Sheet1!\$K\$2:\$K\$3223<\$B6),--(Sheet1!\$CM\$2:\$CM\$3223=C\$4))/J5

..is enough

2) the J5 reference doesn't include a sheet component - should it?

3) "...1st one does not work" - tell us exactly what result you're getting - an error message? the 'wrong' number??

Ah, i know why you needed to know what was in J5 now, that was the problem... (I should have noticed sooner that I was overlapping my formulas)

Previously i was doing this in cell J5

Code:
``=SUM(C5:I5)``
RESULT 384

Now ive changed J5 to this, it works.

Code:
``=SUMPRODUCT(--('Sheet1'!\$K\$2:\$K\$3223>=\$B5),--('Sheet1'!\$K\$2:\$K\$3223<\$B6),--('Sheet1'!\$CM\$2:\$CM\$3223<>C\$4))[/list]``
RESULT 384

Thanks for waking the brain matter back up, its 3am here so I guess now its time to rack up a few Zzzzz's

Cheers guys!

Replies
6
Views
784
Replies
2
Views
321
Replies
1
Views
191
Replies
3
Views
101
Replies
0
Views
124

1,219,918
Messages
6,150,951
Members
450,996
Latest member
darko1515s

### 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.

### Which adblocker are you using?

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

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