Results 1 to 12 of 12
Thread: Sumif formula (Office 97)

20020702, 08:13 #1
 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.

20020702, 08:53 #2
 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.jkpads.com
Professional Office Developers Association

20020702, 13:02 #3
 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

20020702, 15:38 #4
 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

20020703, 06:35 #5
 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.

20020703, 08:31 #6
 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.jkpads.com
Professional Office Developers Association

20020703, 09:26 #7
 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.

20020703, 11:07 #8
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,291
 Thanks
 3
 Thanked 198 Times in 184 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)))))
arrayentered. 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.Regards,
Rory
Microsoft MVP  Excel

20020703, 11:15 #9
 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)

20020703, 11:19 #10
 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 controlshiftenter to enter it.Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20020703, 11:26 #11
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,291
 Thanks
 3
 Thanked 198 Times in 184 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

20020705, 06:41 #12
 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.