Sum of max values in each row

[mwc0914]

Assume I have a range of cells with values

I need to sum the max value of each row, as of each column.
So May total is the max of all rows, but only as of the May column. So row 3 values would not be included in the total, and the max row 6 as of May would be 4.

Looking for a formula in the total row to do the summing of the max values as of that month.
and yes, you can have blank rows in the mix

Thanks

 

[StephenCrump]

Does this work for you?

	A	B	C	D	E
1		April	May	Jun	Jul
2		1	2
3				8	9
4
5
6			4	8
7
8	TOTAL	1	6	18	19
Sheet1

Cell Formulas
Range	Formula
B8:E8	B8	=SUMPRODUCT(SUBTOTAL(4,OFFSET($B2,ROW(B2:B6)-ROW(B2),,,COLUMNS($B8:B8))))

Named Ranges
Name	Refers To	Cells
rng	=Sheet1!$B$2:$E$6	B8:E8

 

[mwc0914]

=SUMPRODUCT(SUBTOTAL(4,OFFSET($B2,ROW(B2:B6)-ROW(B2),,,COLUMNS($B8:B8))))

Wow! Thanks that got it!

 

[mwc0914]

Now let's amp it up a bit....let's say we want to show a planned pct complete of each task over time

Say each task show in column B how much of the project effort it represents.
So say as of June 1, task A should be 50% complete, task B 100% complete, task C 40% complete. etc
Thus Task A as of June 1 represents 2.5% planned completion of the project...(5% in column B * .50 in column E)
Task B is to be 100% complete, representing 20% of the whole project as of June 1.
So if my math is correct, as of June 1 we should be 56.5% planned complete of the project.
I'm looking for the formula that will compute the values highlighted in yellow...the planned completion pct of the project as of that date.

Thanks

 

[Eric W]

That's a relatively small update to the formula:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H
1	Task	% of Project	1-Apr	1-May	1-Jun	1-Jul	1-Aug
2	A	5		20	50	100
3	B	20	100
4	C	50	5	25	40	80	100
5	D	10		50	100
6	E	5				100
7	F	10	20		40		100
8		100	24.5	40.5	56.5	84	100	% thru of project
Sheet12

Cell Formulas
Range	Formula
C8:G8	C8	=SUMPRODUCT(SUBTOTAL(4,OFFSET($C2,ROW(C2:C7)-ROW(C2),,,COLUMNS($C8:C8)))*$B$2:$B$7)/100

2 of our results differ, but I manually checked them, and I think the formula is correct.