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
 

Some videos you may like

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
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,091
Messages
5,545,906
Members
410,711
Latest member
Josh324
Top