# Creating sort proof formulas

1. ## Creating sort proof formulas

I'm sure there is a solution to the following problem however I can't seem to find it. Hopefully someone will be kind enough to guide me in the proper direction.

Basicly I have a sheet which is setup something like below and I want to sum the volumes from company 1 and company 2 aswell as total volumes of each product which is fine until I have a need to resort the sheet after I resort the sheet the totals are adjusted to reflect the new value of the cell range I have in my formula. How do I create a formula that will maintain the correct result regardless of how I sort the data?

DATE Company Product Volume
oct 11 company1 product1 6
oct 12 company2 product1 4
nov 21 company1 product1 5

2. ## Re: Creating sort proof formulas

Try using the SUMIFS function instead of just a SUM. SUMIFS allows you to sum based on a criteria, for example, you can create a formula to sum volume only where company=company1. The way the data is sorted will not affect this formula. The function arguments popup makes it pretty self-explainatory, but post again if you need further help and I can walk you through it.

3. ## Re: Creating sort proof formulas

I tried using the following formula with no luck.

in column F i have the amount of product and in column D I have the product names.
so I am trying to sum everything in column F if column D is "product1"

=SUMIFS(F1:F87,D1:D87,"product1")

4. ## Re: Creating sort proof formulas

Try
``Please Login or Register  to view this content.``
Alf

5. ## Re: Creating sort proof formulas

Might be a compatability issue, try the SUMIF function, in your case it would be =SUMIF(D1:D87,"product1",F1:F87).

Let me know if that one works.

6. ## Re: Creating sort proof formulas

Thanks now how would I go about checking to see if 2 columns are correct before adding a third?

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