Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sumif formula (Office 97)

    I have this formula which should add up some numbers if the data is higher than 0, in order to avoid the #Div/0 message:

    sumif(Sheet1!K6/'[Current exchange rates for Aircare template.xls]Sheet1'!$E$9+Sheet1!L6/'[Current exchange rates for Aircare template.xls]Sheet1'!$E$10, ">0")

    The formula calculates the data in a different currency, by using Exchange rates from another file called "Current exchange rates ..."., however I have an error message and I cannot figure out what is wrong with it. Can anyone help me with this? It is the first time that I am using the Sumif in conjunction with data from two different files/sheets and it seems that I get it all wrong.

    Thanks!
    K.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumif formula (Office 97)

    The SUMIF function can only look at the value of indivudual cells and sum those that meet a single criteria.

    What you need is a simple IF construction. Since the DIV#/0 error is caused when either of the external links equals zero, I tested for those:

    =IF(OR('[Current exchange rates for Aircare template.xls]Sheet1'!$E$9=0,'[Current exchange rates for Aircare template.xls]Sheet1'!$E$10=0),"",
    Sheet1!K6/'[Current exchange rates for Aircare template.xls]Sheet1'!$E$9+Sheet1!L6/'[Current exchange rates for Aircare template.xls]Sheet1'!$E$10)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumif formula (Office 97)

    I constructed the formula that you gave me for another 4 periods data, and the final would look like this:
    (I renamed the Exchange rate file to Test, for easier overwiew of the formula)

    =IF(OR([Test.xls]Sheet1!$E$9=0,[Test.xls]Sheet1!$E$10=0,[Test.xls]Sheet1!$E$11=0,[Test.xls]Sheet1!$E$12=0,[Test.xls]Sheet1!$E$13=0,[Test.xls]Sheet1!$E$14=0),"",Sheet1!K6/[Test.xls]Sheet1!$E$9+Sheet1!L6/[Test.xls]Sheet1!$E$10+Sheet1!M6/[Test.xls]Sheet1!$E$11+Sheet1!N6/[Test.xls]Sheet1!$E$12+Sheet1!O6/[Test.xls]Sheet1!$E$13+Sheet1!P6/[Test.xls]Sheet1!$E$14)

    The results of this gives me an empty cell. I might have made a mistake when I continued the given formula for the remaining 4 months.
    Basically I want to sum up data for 6 months, each of them using a currency exchange rate which is in another file (whereby I take the local currency data and divide by the exchange rate), and in the end to add up the final 6 numbers to a total.

    Thanks a lot,
    K

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Sumif formula (Office 97)

    your formula could be shortened a little:

    =IF([Test.xls]Sheet1!$E$9*[Test.xls]Sheet1!$E$10*[Test.xls]Sheet1!$E$11*[Test.xls]Sheet1!$E$12*[Test.xls]Sheet1!$E$13*[Test.xls]Sheet1!$E$14,"",SUMPRODUCT(Sheet1!K6:P6,1/[Test.xls]Sheet1!$E$9:E$14))

    (I edited it on the fly, so I might be a little off.)

    If this is not yielding what you want, perhaps you can post the relevant part of you workbook?
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumif formula (Office 97)

    Hi John,

    I tried your formula, but I still got the Div/0 error. I made a very small sample of my workbooks, and I put everything in one sheet (in the original I am using different sheets, different files) for easier use. I am attaching it with my comments included.
    Any help would be greatly appreciated.

    Thanks,
    K.
    Attached Files Attached Files

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumif formula (Office 97)

    The formula's that have been given so far obviously tested the wrong cells. You should adjust the formula's so that you test for each cell you you to devide *by* whether they are zero or not. In the example sheet you gave you should use this formula:

    =IF(A3*B3*C3*D3*E3*F3=0,"",B6/A3+B7/B3+B8/C3+B9/D3+B10/E3+B11/F3)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumif formula (Office 97)

    Hi Jan,

    I must be doing something wrong, as the result for the formula you gave me is an empty cell. Of course, if I enter numbers in all the cells that it looks for (A3 until the end), I have the correct summation. However I want to add up the actual cells that have numbers in them, and to ignore the empty ones, as the client does want an updated sum which will change every month, depending of the new entries done...

    Thanks a lot,
    K.

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Sumif formula (Office 97)

    Hi,
    If you rearrange your exchange rate table into rows as in the attached it makes life a lot easier and you can then use an array formula as I have done which should update as you enter new information - the formula is:
    =SUM(OFFSET(A7,0,0,1,MAX((((A7:F7)*(A3:F3))>0)*(CO LUMN(A7:F7))))/OFFSET(A3,0,0,1,MAX(((A7:F7)*(A3:F3)>0)*(COLUMN(A3 :F3)))))
    array-entered. Note that it returns an error if you don't have at least one complete column of data, but I hope that's OK?
    Hope that helps.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sumif formula (Office 97)

    Does this help (see attachment)?

    I put the transposed row data in a hidden column (column H) and used this in an array formula

    =SUM(IF(H6:H11=0,0,B6:B11/H6:H11))

    (confirm with Shift+Ctrl+Enter to make it an array formula)
    Attached Files Attached Files

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumif formula (Office 97)

    Oh, you do want to sum those that do contain something! OK.

    Use this array formula:

    =SUM(IF(ISERROR(B6:B11/TRANSPOSE(A3:F3));"";B6:B11/TRANSPOSE(A3:F3)))

    press control-shift-enter to enter it.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Sumif formula (Office 97)

    Hans,
    That's far too simple and elegant! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumif formula (Office 97)

    Lovely, thank you very much, all your suggestions work! I just have to modify it to work with the real thing, and I have a headache less to deal with, lol.

    Thanks again.

    Bye,
    K.

Posting Permissions

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