Excel - Writing proper SUM formula

0
votes

I am supposed to sum up the total highlighted numbers and add the sum in the red marked cell beside the average number. I am using a basic SUM formula to add the cells. I have a couple thousand lines to do this with and I have to manually change the SUM formula to include the right ranges (as some have 4 numbers to add up, some have 3 and others have 2). Is there a way to write the formula so it SUMS all the numbers up until the Average line above it? So I can use the same formula throughout my sheet and not have to change it for every line?

enter image description here

2
excelexcel-formulaformula

2 Answers

2
votes

No need for Array formulas.

I will assume you are starting in H2. As per your photo in the discussion

=IF(E2 = "Average",SUMIF($E$1:E1,"<>Average",$G$1:G1)-SUM($H$1:H1),"")

And copy down.

enter image description here

1
votes

You could use a variable range within a SUM function, using INDEX and MATCH to find the previous instance of Average.

Something like this - an array formula, so enter with Ctrl+Shift+Enter:

=SUM(B3:INDEX(B$1:B3,IFERROR(MATCH(2,1/(A$1:A3="Average")),0)+1))

enter image description here