# To calculate Column and Rows

1. ## To calculate Column and Rows

Hello Everybody,

from attached sample sheet I am want to calculate and add rows and colomn and tally both, i am managing it by simple way, but looking to proper formula, my sheet is big and calculating manuallhy will be comursome.

attached sheet can explain more

Thankx and regards,

jimmiboy  Register To Reply

2. ## Re: To calculate Column and Rows

It is really unclear what your question is so I am going to guess.

Your "Rate" data is strings. You need to break this into two columns so one column is numbers.

The Total Order should use SUM.

Your math in Total Order Cost doesn't make sense. The top number in the division seems to be the Rate but the bottom number seems pretty random.

 N 6 Total Order Cost 7 =(50/1000)*M7 8 =(35/100)*M8 9 =(25/100)*M9 10 =(40/12)*M10 11 =M11*25

I have left the bottom number in place for you to figure out but the top number is now the Rate. This formula can be made more automatic if you explain where the bottom number comes from.  Register To Reply

3. ## Re: To calculate Column and Rows

Yes, I can change Rate colomn, I can change, but main query is how to calculate individual cost ????  Register To Reply

4. ## Re: To calculate Column and Rows

See attached

in K12

=SUMPRODUCT((K7:K11)*(\$H7:\$H11)/(\$J7:\$J11))  Register To Reply

5. ## Re: To calculate Column and Rows

John,
thkx yr formula works perfectly, howere I am trying to apply other way where its not work work can you pl advice  Register To Reply

6. ## Re: To calculate Column and Rows

It will not work as you mutiplying columns (bold red) by rows ; see attached as per original

=SUMPRODUCT((H4:L4)*(\$D3:\$D7)/(\$E\$3:\$E\$7))

If you want the "new" layout then use

=SUMPRODUCT((\$H4:\$L4)*(\$H\$3:\$L\$3=\$C\$3:\$C\$7)*(\$D\$3:\$D\$7)/(\$E\$3:\$E\$7))  Register To Reply