Thanks:  0
Likes:  0

# Thread: Another SUMIF with Lookup question

1. 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. 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. 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. If you require additional help, provide a concise summary or sample of your data, locations, and the result that you require.

5. 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. 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. 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. 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. 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. 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???

## User Tag List

#### Posting Permissions

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