Sumif formula (Office 97)

Topic

New Reply

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.

Reply | Quote

Replies

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)

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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)

Reply | Quote

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.

Reply | Quote

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)*(COLUMN(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.

Reply | Quote

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)

Reply | Quote

Hans,
That’s far too simple and elegant!

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote