NPV/Sumif

[Philh3325]

Does anyone know how to do an NPV over multiple rows without having to sum
the columns. eg
Y1 Y2 Y3
NHU1 -100 100 100 etc
NHU1 -150 200 300 etc
NHU2 -160 140 300 etc
NHU2 -200 180 120 etc

Complete NPV by looking for NHU , summing each year for the NHU over
multiple rows. Obvious way is to total each NHU them NPV the total, or NPV
each row and sumif NPV's, but I dont want to include totals or NPVs in this
worksheet as its a data sheet.

Thanks

 

[Luke M]

Why not nest the formulas, and have the summations of each NHU inside the NPV
formula?

 

[Fred Smith]

As long as you're not exceeding the limit of NPV parameters, you can use:

=NPV(rate,sumif(A:A,"NHU*",B:B),sumif(A:A,"NHU*",C:C),...)

Regads,
Fred.

 

[Philh3325]

Unfortunately the number of years are 30 and the data is in a different
worksheet so when I start to put the formula together it gets too long for
Xcel to handle. I was going to handle the 30th year as +CF/(1+i)^30 but I
wonder if the whole cashflow can somehow be handled this way rather than
using NPV.