Sumif

[dspeedie1987]

Heres my dilemma.

I am trying to create a tariff whereby, if you put a number in a cell it will come up with the value of another cell. It sounds simple enough, but I need it to do it multiple times. i.e 1=D43, 2=D44

I have got it work unsing the SUMIF function, but I cannot get it to repeat.

Example.

=SUMIF(K16,"1",D43)

I want to be able to put in 2,3,4 etc in K16 and it will bring up the value of another cell.

The other way I have tried is by using Lookup.

Example.

=LOOKUP(K16,{1,2,3,4,5},{6,7,8,9})


This only works if you put in a value, i.e 6,7,8,9. not another cell

Thanks and pardon my ignorance.

[NBVC]

Not sure I follow, but perhaps the Choose() function might help;.


=CHOOSE(K16,D43,D44,D45,D46,D47)

This will pick from D43, D44, etc.. corresponding to position based on what is in K16.

so if K16 is 2, then what is in D44 will be returned, if K16 is 4, then D46 value is returned, etc.

[Waylander]

Hi dspeedie

Looks like a job for the IF function

However you don't say how many possibilities you can put in K16, as the IF function is limited to 7 nested IFs that may be a problem

But as a brief idea, you need to make it look something like this

=IF(K16=1,D43,IF(K16=2,D44,IF(K16=3,D$%,"")))

this can obviously be continued up to the statement IF K16=7

However if you have more possible entries to K16 then it needs a slightly different approach

So have a look if this works for you then all well and good, if you need something else just post back

Also just as a question, is it always a one to one relationship between the K16 entry and the retrieved data or is it sometimes a calculated amount, as this can make a big difference

Any questions just shout

[dspeedie1987]

Thanks very much for your help. It was the IF function that worked.

I think I was on the right lines, it was just my format that was wrong.

Just a bit more information of what I was trying to do.

I work for a shipping company and we import from all over the world.

I wanted to create a tariff that if you put a weight in and where it was coming from it work out how much it would cost.

Because the prices vary from around the would, i wanted it to choose the correct tariff to calculate from. i.e 1 = New York 2= Tokyo.

Weylander,

The data it takes is a calculated value, but it still seems to work.
Most of my tariffs will not exceed 7 origins, but if it did, is it still possible to use this function?


Thanks again.

[Waylander]

Right the answer to the question of more than 7 depends on your version of Excel

Excel 2003 and earlier only allows 7 nested IF statements in one formula

Excel 2007 allows up to 64 nested IF statements in one formula

while it will still be possible to do it in 2003, it would need a different approach, have a look and check which version you have, and let me know if its an earlier version, I'll have another look and give some suggestions

[dspeedie1987]

I have Excel 2003.

I also have another query, if you don't mind helping as well?

It is similar to what you helped with before, which I customised for this.

=IF(K14<45,MAX(G8,K14*H8),IF(K14>=45,K14*I8,IF(K14>=100,K14*J8)))

It works fine except that the last part, IF(K14>=100,K14*J8))) is not being recognised. If I put in a value over 100 it still uses the previous calculation IF(K14>=45,K14*I8

[arthurbr]

I have Excel 2003.

I also have another query, if you don't mind helping as well?

It is similar to what you helped with before, which I customised for this.

=IF(K14<45,MAX(G8,K14*H8),IF(K14>=45,K14*I8,IF(K14>=100,K14*J8)))

It works fine except that the last part, IF(K14>=100,K14*J8))) is not being recognised. If I put in a value over 100 it still uses the previous calculation IF(K14>=45,K14*I8

Please Login or Register  to view this content.

[dspeedie1987]

Thats great, thanks very much.