# Thread: Sumif formula (Office 97)

1. ## 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. ## 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)

3. ## 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. ## 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?

5. ## 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.

6. ## 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)

7. ## 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. ## 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.

9. ## 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)

10. ## 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.

11. ## 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>

12. ## 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
•