Find And Sum

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hi,


    I have a problem at the economy unit that I would be very thankful if you guys could help me solve. The problem is I have an invoice which is divided into a ton of cost centre. I need an easy way to sum the amount for each of the cost centre and then added it another sheet in excel which carry an accountancy order.
    The way I look at how it would work out is something like this,


    All I would like to have to do is add cost centre and amount in sheet two and accounting entry in sheet one, the rest is for excel to handle



    If you find a better way solved this by, feel free to let me know about it.


    I am using Office 2000 at the office.



    Best regards


    GZ

    Re: Find And Sum


    Sorry wrote it in a hurry I see, ment accounts department :)



    Sorry countif does not cut it. I need to calculate the number each cost centre represent. As in my example it is 5532 + 6000 for cost centre 566

    Re: Find And Sum


    I dont think that would work either as I understand the functon.


    If I have cost centre 566 in sheet one I would then like the function to find each cost centre 566 in column A in sheet 2 and then sum the amounts it finds that is in column B ( then add it to column C in sheet one) - cost centre shall not be calculated in to the sum - only the amount it represent in column B shall

    Re: Find And Sum


    I never said that. I said, “I dont think that would work either as I understand the functon.” It is a huge difference.


    I have some questions if it is ok.

    • Why is there a + sign in front of the function?
    • How do I change the function so it goes down to the bottom of column A and B – not just to 100 ?
    • I am not able to fill the function down, I get a missing error
    • How does it look like as a countif function – I see lost more knowledge on excel than I thought

    Re: Find And Sum


    1) The plus sign is an old syntax; it is equivalent to the equal sign. Most ppl enter an equal sign and their formula, I enter the plus sign instead. Excel adds the equal sign. In a nut shell, the plus sign means nothing.


    2) With the sumproduct your ranges have to be equal in all statements and you cant use entire column references. I just saw on this forum, however, that if you are using excel 2007 that you can use entire column references. Other wise you would have to have a range that was i.e. A1:A65536, you couldn’t use A:A. You can use an entire column reference in the sumif function though.


    3) Not sure about the missing error. Possibly because you didn’t make Sheet1!B3 absolute before dragging it down?


    4) According to the sample I posted, this is the sumif


    =SUMIF(Sheet2!$A$5:$A$9,Sheet1!B3,Sheet2!$B$5:$B$9). Here is some details on the sumif.
    Syntax


    SUMIF(range,criteria,sum_range)


    Range is the range of cells that you want evaluated by criteria.


    Criteria is the criteria in the form of a number, expression, or text that defines which cells will be added. For example, criteria can be expressed as 32, "32", ">32", or "apples".


    Sum_range are the actual cells to add if their corresponding cells in range match criteria. If sum_range is omitted, the cells in range are both evaluated by criteria and added if they match criteria.

    Re: Find And Sum


    Appreciate it a lot.


    I will go with sumif sounds to me like the better one.



    If I would like the entire A and B column to be active then it look like this, right ?
    =+SUMIF(Sheet2!$A:$A;Sheet1!$B15;Sheet2!$B:$B)

    Re: Find And Sum


    Excel 2007 is giving a #invalid (not sure if it is the correct translation – using a non English version) in the "Function Arguments" window. But in excel 2000 it is not.
    Will A:A work properly in the SUMIF function in both versions of excel?[hr]*[/hr] Auto Merged Post;[dl]*[/dl]I would appreciate help with another problem as well.
    I have a debit and a credit column. If one cell in debit is lager than a cell in credit than I would like excel to calculate the difference and add the difference in a cell in the correct column.
    For example:


    Column A Column B
    ====== ======
    Debit Credit
    299,9 300
    0,1


    Column B contain 10 cent more than column A.
    Excel shall then add 0,1 in a cell below 299,9.
    If Column A would contain a larger amount then excel should add the difference in row below column b. The row will be fixed. All excel have to do is to know what column to add the difference in.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login