IF & VLOOKUP functions

[Emma]

Hi all. I'm working on a large spreadsheet of financial
data and currently have the following formula in one
column: =IF(P4>0,VLOOKUP(C4,InvRevMarMutFds!
$B$1:$K$69,9,FALSE)/M4,"0%") P4 may or may not have a
value depending upon whether the account holds mutual
funds. If it does, I want Excel to find the exact match
of that account number (in C4 here) in the specified range
on the second worksheet, then return the value in column 9
of the lookup range, divide it by the account value (M4)
and return the percentage of value that that fund
represents. This is working. On the second worksheet,
though, there may be several rows of occurance for each
account number depending upon the number of funds held.
How can I ask Excel to look for all occurances of the
account number and return the sum of values in column 9 of
the range? Thanks in advance for your suggestions.

 

[Guest]

Try the Get DSUM function.

 

[Frank Kabel]

Hi Emma
try
=IF(P4>0,SUMIF('InvRevMarMutFds'!$B$1:$B$69,C4,'InvRevMarMutFds'!$J$1:$
J$69)/M4,0)
format this cell as percentage (note: I also change "0%" to 0 as this
will return a value and not a string)

Frank

 

[Emma]

Hi Frank,

That was exactly what it needed - thank you so much!

Emma