sum & vlookup

  • Thread starter Thread starter Jodi
  • Start date Start date
J

Jodi

Dear Advanced Excel users,

I am trying to sum the lookup value results of specific
references in a table into one cell of another worksheet.
The formula listed at the bottom of the post is the
lastest version, but I need to add an ISNA or ISERROR at
the front and have run out of room in the cell. I've
also tried sum if syntax and a named array as my lookup
value. Do you have any suggestions?

Thanks for any assistance.
Jodi

sample data
col A col b col c
1001500002 Closed USE 1001500007 0
1001500003 Closed USE 1001500007 0
1001500051 PROJECT MANAGEMENT 646294.59
1001500061 ADMINISTRATION 1052124.52
1001500062 STRATEGIC TECH COMM 468841.64
1001500063 CONNECTIVITY SEGMENT 41051.88
1001500066 APPLICATIONS ENGINEERING 1047340.75
1001500678 t ELECTRICAL 0
1001500679 t MECHANICAL 0
1001500680 t SOFTWARE 0
1001500681 t SYSTEMS 0
1001500682 t MFG. SUPPORT 0
1001500683 t PROJECT MANAGEMENT 0
1001500690 MANUFACTURING 343047.03
1001500691 MECHANICAL ENGINEERING 359571.1
1001500692 SOFTWARE ENGINEERING 1108197.14
1001500693 ELECTRICAL ENGINEERING 455509.37
1001500694 SYSTEMS 660019.38
1001500695 LSEGMENT 2581058.46

formula: SUM(VLOOKUP
("1001500857",'P:\[ForecastSummary.xls]results'!
$B:$D,3,0),VLOOKUP("1001500858",'P:\[ForecastSummary.xls]
results'!$B:$D,3,0),VLOOKUP
("1001500695",'P:\[ForecastSummary.xls]results'!
$B:$D,3,0),VLOOKUP("1001500846",'P:\[ForecastSummary.xls]
results'!$B:$D,3,0),VLOOKUP
("1001500856",'P:\[ForecastSummary.xls]results'!
$B:$D,3,0),VLOOKUP("1001500847",'P:\[ForecastSummary.xls]
results'!$B:$D,3,0),VLOOKUP
("1001500848",'P:\[ForecastSummary.xls]results'!
$B:$D,3,0),VLOOKUP("1001500849",'P:\[ForecastSummary.xls]
results'!$B:$D,3,0),VLOOKUP
("1001500867",'P:\[ForecastSummary.xls]results'!
$B:$D,3,0))
 
Hello Jodi

If I have understood you properly, here is one way
to do it.

Replace 1,3,5 etc with "1001500857","1001500858","1001500695" etc.
The data in B:B must be in ascending order for the LOOKUP-function to
work.

=SUM(IF(ISNA(MATCH({1,3,5,6,8,9,12,23,34},'P:\[ForecastSummary.xls]results'!
B:B,0)),0,
LOOKUP({1,3,5,6,8,9,12,23,34},'P:\[ForecastSummary.xls]results'!B:B,'P:\[For
ecastSummary.xls]results'!D:D)))

The formula is an array formula and must be entered
with <Shift><Ctrl><Enter>, also if edited later. If done
correctly, Excel will display the formula in the formula bar
enclosed in curly brackets { } Don't enter these brackets
yourself. They are Excel's way of showing, that the formula
is an array formula.

You can also enter the values "1001500857","1001500858","1001500695" etc.
in a range, e.g. F2:F10 and use the formula like this:

=SUM(IF(ISNA(MATCH(F2:F10,'P:\[ForecastSummary.xls]results'!B:B,0)),0,
LOOKUP(F2:F10,'P:\[ForecastSummary.xls]results'!B:B,'P:\[ForecastSummary.xls
]results'!D:D)))

again to be entered with <Shift><Ctrl><Enter>


--
Best Regards
Leo Heuser

Followup to newsgroup only please.


Jodi said:
Dear Advanced Excel users,

I am trying to sum the lookup value results of specific
references in a table into one cell of another worksheet.
The formula listed at the bottom of the post is the
lastest version, but I need to add an ISNA or ISERROR at
the front and have run out of room in the cell. I've
also tried sum if syntax and a named array as my lookup
value. Do you have any suggestions?

Thanks for any assistance.
Jodi

sample data
col A col b col c
1001500002 Closed USE 1001500007 0
1001500003 Closed USE 1001500007 0
1001500051 PROJECT MANAGEMENT 646294.59
1001500061 ADMINISTRATION 1052124.52
1001500062 STRATEGIC TECH COMM 468841.64
1001500063 CONNECTIVITY SEGMENT 41051.88
1001500066 APPLICATIONS ENGINEERING 1047340.75
1001500678 t ELECTRICAL 0
1001500679 t MECHANICAL 0
1001500680 t SOFTWARE 0
1001500681 t SYSTEMS 0
1001500682 t MFG. SUPPORT 0
1001500683 t PROJECT MANAGEMENT 0
1001500690 MANUFACTURING 343047.03
1001500691 MECHANICAL ENGINEERING 359571.1
1001500692 SOFTWARE ENGINEERING 1108197.14
1001500693 ELECTRICAL ENGINEERING 455509.37
1001500694 SYSTEMS 660019.38
1001500695 LSEGMENT 2581058.46

formula: SUM(VLOOKUP
("1001500857",'P:\[ForecastSummary.xls]results'!
$B:$D,3,0),VLOOKUP("1001500858",'P:\[ForecastSummary.xls]
results'!$B:$D,3,0),VLOOKUP
("1001500695",'P:\[ForecastSummary.xls]results'!
$B:$D,3,0),VLOOKUP("1001500846",'P:\[ForecastSummary.xls]
results'!$B:$D,3,0),VLOOKUP
("1001500856",'P:\[ForecastSummary.xls]results'!
$B:$D,3,0),VLOOKUP("1001500847",'P:\[ForecastSummary.xls]
results'!$B:$D,3,0),VLOOKUP
("1001500848",'P:\[ForecastSummary.xls]results'!
$B:$D,3,0),VLOOKUP("1001500849",'P:\[ForecastSummary.xls]
results'!$B:$D,3,0),VLOOKUP
("1001500867",'P:\[ForecastSummary.xls]results'!
$B:$D,3,0))
Click to expand...
 
You're welcome, Jodi, and thank you
for the feedback :-)

It's a very informal forum, so just "Leo" is fine :-)

LeoH
 
Back
Top