sumproduct and division

[evanslee]

Hi,

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

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

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

=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

 

[Scott Huish]

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]

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.

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

=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]

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]

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

=SUM(C5:I5)

RESULT 384

Now ive changed J5 to this, it works.

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

RESULT 384

 

[evanslee]

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!