LookUp & SumIF?

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

I need a formula that will look up a code reference on a
separate sheet and add all the amounts entered against
that code.
The formula will be entered in cell D2 on sheet1, the
codes are entered in ColC sheet2, and the amounts to be
summed for each code are in ColE sheet2.
Any help would be appreciated.
Regards
Joe
 
Frank,

this is the greatest formlua in the history of excel. :-)

Can you tell me if there is a similar formula that will pick up TEXT in the same manor

Just doing this has solved 99% of my problem (and I am SO grateful and relieved) but I can't get the cells taht have TEXT in them to come over.

THANKS IN ADVANCE, SO MUCH!

Mark B
 
Hi Mark
with Text how do you want to transfer it (you can't sum text). Maybe
VLOOKUP is what you're looking for. as a starting point have a look at
http://www.mvps.org/dmcritchie/excel/vlookup.htm

You may post back with a little bit more detail what you're trying to
achieve :-)

--
Regards
Frank Kabel
Frankfurt, Germany

Mark B said:
Frank,

this is the greatest formlua in the history of excel. :-)

Can you tell me if there is a similar formula that will pick up TEXT in the same manor?

Just doing this has solved 99% of my problem (and I am SO grateful
Click to expand...
and relieved) but I can't get the cells taht have TEXT in them to come
over.
 
What I was originally trying to do was lookup a code on a separate sheet and then pull in the corresponding amount within that row. It wasn't a sum, but that last formula worked brilliantly.

eg. =SUMIF('DB Xfer'!A:A,'2004 Staff'!A6,'DB Xfer'!B:B
=SUMIF('DB Xfer'!B:B,'2004 Staff'!B5,'DB Xfer'!C:C
etc..

However, the final column is a text field. And the above formula did not pull in anything.

Any suggestions? You made a very painful weekend MUCH easier to sleep in! :)

Thanks again! M
 
Sorry, my example had a mistake in it. This is an example of what I am doing...

eg

This goes in Column E
=SUMIF('DB Xfer'!A:A,'2004 Staff'!A6,'DB Xfer'!B:B

This goes in Column F
=SUMIF('DB Xfer'!A:A,'2004 Staff'!A6,'DB Xfer'!C:C

etc..
 
Hi
the question remains. What to you want to pull over. Maybe you can post
some example data (in plain text please). You can't sum text entries.
So the formula below can't pull anything.

So just post some example data and your expected result!

--
Regards
Frank Kabel
Frankfurt, Germany

Mark B said:
What I was originally trying to do was lookup a code on a separate
Click to expand...
sheet and then pull in the corresponding amount within that row. It
wasn't a sum, but that last formula worked brilliantly.
 
Hi
better would e you'd explain what you're trying to achieve. In your
case maybe the following is what you're looking for:
=VLOOKUP(A5,'DB Xfer'!A1:F1000,6,0)
 
Thanks SO MUCH for this, Frank...

NeedsInfoSheet HasInfoShee
A B C A B
1 101 ? ? 101 $100 Harr
2 102 ? ? 103 $200 Joa
3 103 ? ? 105 $300 Marti
4 104 ?
5 105 ?

Putting =SUMIF('HasInfoSheet'!A:A,'NeedsInfoSheet'!A1,'HasInfoSheet'!B:B) in B1 on the NeedsInfoShee
brings over $100 from the HasInfoSheet.

But putting =SUMIF('HasInfoSheet'!A:A,'NeedsInfoSheet'!A1,'HasInfoSheet'!C:C) in C1 on the NeedsInfoShee
does not bring word Harry from the HasInfoSheet

(FYI, the HasInfoSheet is continually updated by other people

Does that help?
 
Hi
put the following formulas on your 'NeedsInfoSheet':
B1:
=VLOOKUP(A1,'HasInfoSheet'!$A$1:$C$1000,2,0)

C1:
=VLOOKUP(A1,'HasInfoSheet'!$A$1:$C$1000,3,0)

copy both formulas down

--
Regards
Frank Kabel
Frankfurt, Germany

Mark B said:
Thanks SO MUCH for this, Frank...

NeedsInfoSheet HasInfoSheet
A B C A B C
1 101 ? ? 101 $100 Harry
2 102 ? ? 103 $200 Joan
3 103 ? ? 105 $300 Martin
4 104 ? ?
5 105 ? ?

Putting
Click to expand...
=SUMIF('HasInfoSheet'!A:A,'NeedsInfoSheet'!A1,'HasInfoSheet'!B:B) in B1
on the NeedsInfoSheet
brings over $100 from the HasInfoSheet.

But putting
Click to expand...
=SUMIF('HasInfoSheet'!A:A,'NeedsInfoSheet'!A1,'HasInfoSheet'!C:C) in C1
on the NeedsInfoSheet
 
Actually, Frank, this did it...

=VLOOKUP(A5,'DB Xfer'!$A$1:$F$2000,6,0

which, again, is simply amazing

Any idea how to get the one's that come up with #N/A! to not show the result

I cannot thank you enough!
 
Hi
try
=IF(ISNA(VLOOKUP(A5,'DB Xfer'!$A$1:$F$2000,6,0)),"",VLOOKUP(A5,'DB
Xfer'!$A$1:$F$2000,6,0))
 
=IF(ISNA(VLOOKUP(A5,'DB Xfer'!$A$1:$F$2000,6,FALSE)=TRUE),"",VLOOKUP(A5,'DB Xfer'!$A$1:$F$2000,6,FALSE)

That did it!!

Google is so handy...

unless you know a less wordy way to do it, Frank, I am all set, and can now go to the dog park with my Shepard this weekend instead of sitting in front of this damn computer on a Sunday!!

I can't thank you enough!!!!
 
Hi Mark
you can delete the
=TRUE
part of your formula

--
Regards
Frank Kabel
Frankfurt, Germany

Mark B said:
=IF(ISNA(VLOOKUP(A5,'DB Xfer'!$A$1:$F$2000,6,FALSE)=TRUE),"",VLOOKUP(A5,'DB
Xfer'!$A$1:$F$2000,6,FALSE))

That did it!!!

Google is so handy...

unless you know a less wordy way to do it, Frank, I am all set, and
Click to expand...
can now go to the dog park with my Shepard this weekend instead of
sitting in front of this damn computer on a Sunday!!!
 
You can delete the TRUE and make things a bit more efficient using
ISNA(MATCH), but you'll only see the difference if you have a lot of
these:

=IF(ISNA(MATCH(A5,'DB Xfer'!$A$1:$A$2000,FALSE)),"",VLOOKUP(A5,'DB
Xfer'!$A$1:$F$2000,6,FALSE))
 
Back
Top