# To calculate % based on subtotal

1. ## To calculate % based on subtotal

Dear All,

PFA the file for your ref.

I need to calculate % in col H.
Doing it manually is very time consuming since data is huge.

2. ## Re: To calculate % based on subtotal

Hi,

Put this in H9 and pull it down.

=+G9/SUMIF(\$D\$9:\$D\$21,D9,\$G\$9:\$G\$21)

3. ## Re: To calculate % based on subtotal

Marvin,

Sorry to tell u that formula does not work.
It does not give the desired result as mentioned in col. H.

4. ## Re: To calculate % based on subtotal

In "H9"
Formula:
`Please Login or Register  to view this content.`

In "H17"
Formula:
`Please Login or Register  to view this content.`

Copy paste according data record.

5. ## Re: To calculate % based on subtotal

Sorry brother.
Currently I have put this formula only.

In my data, there are approx 25000 rows.
So this formula of yours would be time consuming and too manual.

Is there any other formula which can reduce my manual work?

6. ## Re: To calculate % based on subtotal

Thanks dear, in this case can you share / attach your file with desired required result.

7. ## Re: To calculate % based on subtotal

Try this. I am not so sure it will perform well over 25,000 rows.

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

Note: I tried without success to get this to work.
Formula:
`Please Login or Register  to view this content.`
It didn't and I am mystified why. According to my online source MATCH in Excel 2003 should accept wildcards. It didn't in your upload.

Update: This works. I suspect it will perform best of all.
Formula:
`Please Login or Register  to view this content.`

8. ## Re: To calculate % based on subtotal

If your data record more than 25000. I suggest you to change little bit data structure.
Refer attach file.
In this file i have created summary sheet with unique "Sol ID" & Sol Desc" & sum of total with sumifs formula.
In Data sheet "F2" formula is :
Formula:
`Please Login or Register  to view this content.`
copy paste down.

In this case you avoid manually work & time saving.

9. ## Re: To calculate % based on subtotal

Thanks a tonne....

10. ## Re: To calculate % based on subtotal

You are welcome. Thank you for the feedback and marking this Solved.

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1