Another SUMIF with Lookup question

RaoulF

New Member
Joined
Mar 4, 2002
Messages
17
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??:confused:

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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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
 
Upvote 0
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
 
Upvote 0
If you require additional help, provide a concise summary or sample of your data, locations, and the result that you require.
 
Upvote 0
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 :cool:.

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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top