sumproduct and division

evanslee

New Member
Joined
Sep 15, 2006
Messages
19
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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Sep 15, 2006
Messages
19
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]
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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
Joined
Sep 15, 2006
Messages
19
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
Joined
Sep 15, 2006
Messages
19
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!
 

Forum statistics

Threads
1,140,913
Messages
5,703,131
Members
421,277
Latest member
abudgen

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