Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Another SUMIF with Lookup question

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have been struggling for a while over a formula that sums the contents of the cells in a table which have rows headed = "Net"&"*"&"Debt" [Where * may be "Senior" or "Mezzanine"] and where the column is the column headed by a date (as selected by the user in another cell).

    I have been playing around with sumifs (but struggling to get it to 'lookup' the rows that I'm interested in). I also tried to use an index/match to return the sum_range but I just got a #REF. I wonder if I need a D-type formula or an array, but I'm not too hot on them??

    Perhaps I'm just being brain dead at this time on a Friday pm, but any help on this would be apreciated.

    Apologies if this has already been dealt with, but I can't find the answer on the board

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,425
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    If I read your question correctly, the following will give the sum.

    =SUMPRODUCT((A2:A8={"Net Senior Debt","Net Mezzanine Debt"})*(D2:D8=B1)*(C2:C8))

    Look for Net Senior Debt and Net Mezzanine DEbt in Col A and criteria date in Col D and sum numbers in Col C.

    1. revise ranges as necessary
    2. expand to cover a date range


  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Dave.

    The date is in the header row of the table, not in another column. Am I right in saying that this solution relies on the information all being in identical columns?

    The debt values will change over time, so I'm just trying to allow the user to pick up the total debt at a selected date.

    Cheers

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,425
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    If you require additional help, provide a concise summary or sample of your data, locations, and the result that you require.

  5. #5
    Guest

    Default

    The table is a cashflow headed up with dates in row 1 (A to M) with income/ expense/ debt categories in column A (say rows 2 to .

    I would like to search for those rows that contain net debt (there could be several types) as at a requested date.

    The best that I have come up with is:
    =SUM(OFFSET((INDEX(A3:A8,MATCH("Net"&"*"&"Debt",A3:A8,0),0)),0,MATCH(A11,B1:M1,0),COUNTIF(A1:A8,"Net"&"*"&"Debt")))

    Where: The A3:A8 contains the row headings (including rent, interest, Net "*" Debt)
    A11 contains the date to be looked up
    B1:M1 contain the quarter ends heading each column

    This does now work, but this cannot be the most elegant way to simply lookup several entries from a table. Incidently it's just half of the equation that I'm performing so I'm keen to keep it short.

    Hope this is clearer now

  6. #6
    New Member
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Looks like I got timed out there - the above was my response and the smiley was meant to be an 8

    Thanks for any excel wizards attention to this.

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,425
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    You could use Vlookup with a nested Lookup
    =VLOOKUP(A2,A2:F8,LOOKUP(A11,B1:F1,{2,3,4,5,6}),FALSE)

    A2 has "Net Senior Debt"
    If you have just a few debt categories,
    replicate the formula and add them.

    Expand the ranges cited in the formula.

    You should be able to do this with Index and Match. Try your formulas initially without wildcards.

    Good luck

    Dave

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    North Alabama, USA
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Go and look at http://www.cpearson.com/excel/array.htm for excellent discussion of array formulas. A.K.A. CSE or Control Shift Enter formulas. You would need one for each debt type and date combo. for example if type is in column A, the value in Column B through Z has the date in row 1 and money in row 2 through whatever. "{=sum(($A2:$A100="Senior")*$B2:$B100)}" Notice the {} the only way to get them is by CSE, not enter.

    HTH

    Rocky...

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,425
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    Try =SUMPRODUCT((LEFT(A2:A8,3)="Net")*(B1:G1=A11)*(B2:G8))

    Revise the Left(.....) to select the
    items that you wish to aggregate.
    Effectively a "wildcard".
    Single items
    =SUMPRODUCT(((A2:A8)="Prepaids")*(B1:G1=A11)*(B2:G8)). More convenient with a cell ref.

  10. #10
    Guest

    Default

    Thanks guys.

    Rocky, unless I'm mistaken your solution requires an array formula for each date, which could also be done by a vlookup I think, with multiple rows (containing different debt types in my case) being covered by a sumif. Perhaps you're suggesting that I carry this solution in another row and then use a vlookup on this row to pick up the relevant date? That would work too. I might play around with the array possibility though - thanks for the link.

    Dave, I'm not getting the sumproduct formula to work. Help says that "All arrays must have the same dimensions" which might be the problem, although I'm getting #N/A and not #Value, which it says is the result from non-identical arrays??

    Anyone know about using the intersection of 'labels' - I'm sure I've read about that somewhere before???

    Thanks in advance.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •