# 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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### Scott Huish

##### MrExcel MVP
Hi, welcome to the board!

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

#### evanslee

##### New Member
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]

##### MrExcel MVP
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??

#### evanslee

##### New Member
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

#### evanslee

##### New Member
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
4
Views
112
Replies
6
Views
117
Replies
10
Views
146
Replies
7
Views
102
Replies
2
Views
114