# Horizontal Sum Calculation posting results Vertically

1. ## Horizontal Sum Calculation posting results Vertically

Dear All,

I am having problems of creating a formula that essentially would allow me to calculate a sum by column, while on a different sheet having a vertical output.
At the moment I do the following:

In Q6 =SUM('Raw Data'!AH3:AH225)/SUM('Raw Data'!\$AH\$3:\$AQ\$225)
In Q7 =SUM('Raw Data'!AI3:A225)/SUM('Raw Data'!\$AH\$3:\$AQ\$225)
And so on....

Essentially "SUM('Raw Data'!AH3:AH225)" are all the values of AH and "SUM('Raw Data'!\$AH\$3:\$AQ\$225)" is the total of the columns AH - AQ.
I changed AH -> AI by hand, and I am also aware that I just could do a sum calculation at the end of row 225, but I would like to be able to do it via formula as well.

I know there has to be a simple solution, but my experiments via "=INDEX" haven't been successful so far.

Thank you,

Christoph

2. ## Re: Horizontal Sum Calculation posting results Vertically

Hi Christoph,

Welcome to the Excel Forum,

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

3. ## Re: Horizontal Sum Calculation posting results Vertically

Dear shukla,

I posted an example with the relevant data

Regards,

Christoph

4. ## Re: Horizontal Sum Calculation posting results Vertically

Try

B3
Formula:
`Please Login or Register  to view this content.`

And drag down and format the cells with "%"

5. ## Re: Horizontal Sum Calculation posting results Vertically

In Sheet2

in B3

=SUM(INDEX(Sheet1!\$A\$3:\$E\$42,,ROWS(\$1:1)))/SUM(Sheet1!\$A\$3:\$E\$42)

Copy down

6. ## Re: Horizontal Sum Calculation posting results Vertically

Thx Guys,

It works. Just one question why do I put " Rows(\$1:1)" into column_num?

Regards,

Christoph

7. ## Re: Horizontal Sum Calculation posting results Vertically

Originally Posted by Musiclover119
Thx Guys,

why do I put " Rows(\$1:1)" into column_num?
It is just because when formula will go down Rows(\$1:1) change into Rows(\$1:2) that means 2 and Index array will understand now second column which column B need to work.

I had use match the alphabet and let consider in which column that particular alphabet is stand instead of rows

Hope you understood..

8. ## Re: Horizontal Sum Calculation posting results Vertically

INDEX has format Range, Row, Column and in the formula below the ROWS() is in the COLUMN parameter

=SUM(INDEX(Sheet1!\$A\$3:\$E\$42,,ROWS(\$1:1)))/SUM(Sheet1!\$A\$3:\$E\$42)

the ROWS(\$1:1) acts a a "counter" so in B3 it has a value of 1, in B4 a value of 2 etc so it selects the data in the first column of the INDEX range, then the second and so on.

So we select from column A, then column B .....

Hope this helps.

9. ## Re: Horizontal Sum Calculation posting results Vertically

Thank you. That helps

10. ## Re: Horizontal Sum Calculation posting results Vertically

There are currently 1 users browsing this thread. (0 members and 1 guests)