formulae problems:HELP

hello there, i have jst started a new I.T job and need some help with excel,heres my problem, if cell A = 5014 then l - m. under a lot of pressure here can somone please help.

We don't quite have enough information for a complete answer, but here's something to work with:

Assuming your values begin in Cell A1, put this in another column on row 1 and copy down (eg. in Cell B1):

=if(A1=5014,L1-M1,0)

You didn't say what to do if Col A doesn't =5014, so that formula returns a zero.

Does that help?

Ron

thanks fro ure reply, i have 2 different values in collum A,5014 & 5015,stretch over about 40 rows. now i want the values which in l & m which are in the same rows as the 5014's to be included in the calculation, i just cant get the formulae to work.

cheers

Are you looking to summarize all L's minus M's for rows where Column A equals 5014?

Ron

yes ron, im looking for the result of l -m to be the resut of my formulae where l + m are in the same row as any Cell A:5014's which may be on the spreadsheet.

cheers again.

OK...see if I'm on the right track here.

Put one of these formulas in a cell anywhere but in columns A, L, or M:

=SUMIF($A$1:$A$40,5014,$L$1:$L$40)-SUMIF($A$1:$A$40,5014,$M$1:$M$40)

=SUMPRODUCT(--($A$1:$A$40=5014)*(($L$1:$L$40)-($M$1:$M$40)))


Does that help?

Ron

thanks ron ure the man, the top one works, havent tried the 1 below it.

i cant thank u enough.

any chance of talking me through that formulae

=SUMIF($A$1:$A$40,5014,$L$1:$L$40)-SUMIF($A$1:$A$40,5014,$M$1:$M$40)

Sure....

=SUMIF($A$1:$A$40,5014,$L$1:$L$40)-SUMIF($A$1:$A$40,5014,$M$1:$M$40)

Try thinking of the SUMIF like this:
=SUMIF(
Find items in this range,
That match this value,
When you find them...add the corresponding items from this range)

So, in your example, the first SUMIF:
=SUMIF($A$1:$A$40,5014,$L$1:$L$40)

Works this way:
=SUMIF(
Find Items in this range: $A$1:$A$40,
That match: 5014,
When you find them...add the corrsponding items from this range: $L$1:$L$40)


The first SUMIF adds the total matched values from Column L.
The 2nd SUMIF subtracts the total matched values from Column M.

Does that help?

Ron

thanks ron thats great, ill b sure to keep an eye out for u if i have problems in the future.

cheers

steven