MAX across different worksheets

D

Daesthai

What would be the proper syntax to sume the largest number from the same
range of cells on different sheets? I've tried a couple different ways, none
have worked yet. Here's the current version, but I'm not sure what part of
it is wrong.

=SUM('Jan:Jun(MAX(C19:C1047))
 
F

Franz Verga

Nel post:[email protected],
Daesthai said:
What would be the proper syntax to sume the largest number from the
same range of cells on different sheets? I've tried a couple
different ways, none have worked yet. Here's the current version,
but I'm not sure what part of it is wrong.

=SUM('Jan:Jun(MAX(C19:C1047))
Click to expand...

Hi Daesthai,

I think in this case you should use a formula like this:

=SUM(MAX(Foglio1!A1:A8),MAX(Foglio2!A1:A8),MAX(Foglio3!A1:A8))


--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.


Thanks in advance for your feedback.


Ciao


Franz Verga from Italy
 
J

JMB

You could put the MAX function on each worksheet (let's say C18) and use
=SUM(Jan:Jun!C18)

I like to put empty worksheets called START and END on either side of the
target worksheets and use
=SUM(Start:End!C18).

For something different, this also seems to work okay
=SUMPRODUCT(SUBTOTAL(4,INDIRECT({"Jan","Feb","Mar","Apr","May","Jun"}&"!C19:C1047")))

but I have to caution that this formula will not update if your target
ranges move (or have rows/columns inserted in the data). Also, Indirect is
volatile (it recalculates every time excel does), so too many of these will
adversely affect performance.
 
D

Daesthai

Thank you both!

JMB said:
You could put the MAX function on each worksheet (let's say C18) and use
=SUM(Jan:Jun!C18)

I like to put empty worksheets called START and END on either side of the
target worksheets and use
=SUM(Start:End!C18).

For something different, this also seems to work okay
=SUMPRODUCT(SUBTOTAL(4,INDIRECT({"Jan","Feb","Mar","Apr","May","Jun"}&"!C19:C1047")))

but I have to caution that this formula will not update if your target
ranges move (or have rows/columns inserted in the data). Also, Indirect is
volatile (it recalculates every time excel does), so too many of these will
adversely affect performance.
Click to expand...
 
Top