formulae problems:HELP

[stevenmorrison]

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.

 

[Ron Coderre]

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

Assuming your values begin in Cell A1, put this in another column o
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 formul
returns a zero.

Does that help?

Ro

 

[stevenmorrison]

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

 

[Ron Coderre]

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

Ron

 

[stevenmorrison]

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.

 

[Ron Coderre]

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

Put one of these formulas in a cell anywhere but in columns A, L, o
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?

Ro

 

[stevenmorrison]

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)

 

[Ron Coderre]

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

 

[stevenmorrison]

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

cheers

steve