Another SUMIF with Lookup question

RaoulF · Mar 8th, 2002, 10:52 AM

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

Dave Patton · Mar 8th, 2002, 11:53 AM

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

RaoulF · Mar 8th, 2002, 12:01 PM

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

Dave Patton · Mar 8th, 2002, 12:29 PM

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

Mar 8th, 2002, 04:00 PM

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

RaoulF · Mar 8th, 2002, 04:06 PM

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.

Dave Patton · Mar 8th, 2002, 05:26 PM

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

Rocky E · Mar 8th, 2002, 10:09 PM

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

Dave Patton · Mar 9th, 2002, 08:24 AM

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.

Mar 9th, 2002, 05:29 PM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 8th, 2002, 10:52 AM	#1
RaoulF New Member Join Date: Mar 2002 Location: London, UK Posts: 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?? 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

Mar 8th, 2002, 11:53 AM	#2
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	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

Mar 8th, 2002, 12:01 PM	#3
RaoulF New Member Join Date: Mar 2002 Location: London, UK Posts: 17	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

Mar 8th, 2002, 12:29 PM	#4
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	If you require additional help, provide a concise summary or sample of your data, locations, and the result that you require.

Mar 8th, 2002, 04:00 PM	#5
Guest Posts: n/a	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

Mar 8th, 2002, 04:06 PM	#6
RaoulF New Member Join Date: Mar 2002 Location: London, UK Posts: 17	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.

Mar 8th, 2002, 05:26 PM	#7
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	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

Mar 8th, 2002, 10:09 PM	#8
Rocky E Board Regular Join Date: Feb 2002 Location: North Alabama, USA Posts: 105	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...

Mar 9th, 2002, 08:24 AM	#9
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	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.

Mar 9th, 2002, 05:29 PM	#10
Guest Posts: n/a	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.