Can SUMPRODUCT change the range it uses?

[joeq]

Hi all - I'm wondering about a variation of SUMPRODUCT:

If this is typical.............

=SUMPRODUCT(--($A20:$A58=$H$2),$H20:$H58,M106:M144)

What about the scenario where I don't need to match H2 to A20:A58 at all, but rather I just need a way to say:

If H2 = "Home", then use H20:H58 where I use it above.
If H2 = "Car", then use I20:I58 instead.
If H2 = "Lawn", then use J20:J58.

I don't think I can nest an IF since I have 10 possible H2's and IF only allows 7 nests.

Thanks, Joe

 

[acw]

Hi

Assuming that you have Home, Car and Lawn in the range A1:A3 and H20:H58 in B1, I20:I58 in B2 and J20:J58 in B3 then try

=SUMPRODUCT(--($A20:$A58=$H$2),INDIRECT(VLOOKUP(H2,A1:B3,2,FALSE)),M106:M144)

Tony

 

[Aladin Akyurek]

Hi all - I'm wondering about a variation of SUMPRODUCT:

If this is typical.............

=SUMPRODUCT(--($A20:$A58=$H$2),$H20:$H58,M106:M144)

What about the scenario where I don't need to match H2 to A20:A58 at all, but rather I just need a way to say:

If H2 = "Home", then use H20:H58 where I use it above.
If H2 = "Car", then use I20:I58 instead.
If H2 = "Lawn", then use J20:J58.

I don't think I can nest an IF since I have 10 possible H2's and IF only allows 7 nests.

Thanks, Joe

Another less expensive way if the ranges are headed by appropriate names, that is, H19:J19 houses Home, Car, and Lawn as headers...

=SUMPRODUCT(INDEX(H20:J58,0,MATCH(H2,H19:J19,0)),M106:M144)

 

[neeber34]

Similar, but also need to sort by date

Hey guys,

I need to do something similar to the example already posted here, only I also need to choose data by dates.

For example, I have headings across the top, like his "House, Car, Lawn, etc" but I also have a date heading, with dates listing down the rows.

I need to be able to sum all entries for example, a specific date, for house, from a range of cells

Any help would be great! I'm really stuck here.

Thanks

 

[just_jon]

Book1
	A	B	C	D	E	F	G	H
1		Car	House	Lawn	Other		Lawn	12/22/2005
2	12/20/2005	1	2	3	4		522
3	12/21/2005	3	2	4	1
4	12/22/2005	55	44	33	22
5	12/22/2005	123	345	456
6	12/22/2005
7	12/22/2005			33	99
8	12/26/2005	11	22	32	12
9	12/27/2005
10	12/28/2005	11		11
11
Sheet1

 

[neeber34]

This kind of worked for me, but for some reason, it is totalling extra columns, rather than just the matching part.

for example, it would total home and lawn, when it should just be pulling home.

any ideas?

edit: it seems like as soon as I have a number in a column to the right of the column I'm trying to sum, it includes it in the calculation

 

[just_jon]

Can you post the formula as it stands now -- a screenshot of your sheet using Colo's HMTLMaker [ available at the bottom of the page ] would be better.

 

[neeber34]

I can't seem to get the html thing to work, but the formula is:

=SUM(INDEX('Open Orders'!$H$6:$EQ$36000,MATCH('Planning Sheet Template'!C5,'Open Orders'!$F$6:$F$36000,0),MATCH('Planning Sheet Template'!$B2,'Open Orders'!$H$5:$EQ$5,0)):INDEX('Open Orders'!$H$6:$EQ$36000,MATCH('Planning Sheet Template'!C5,'Open Orders'!$F$6:$F$36000),MATCH('Planning Sheet Template'!$B2,'Open Orders'!$H$5:$EQ$5)))

The data is coming from a different sheet than where I'm entering the formula.

[/url][/list]

 

[just_jon]

I can't seem to get the html thing to work, but the formula is:

=SUM(INDEX('Open Orders'!$H$6:$EQ$36000,MATCH('Planning Sheet Template'!C5,'Open Orders'!$F$6:$F$36000,0),MATCH('Planning Sheet Template'!$B2,'Open Orders'!$H$5:$EQ$5,0)):INDEX('Open Orders'!$H$6:$EQ$36000,MATCH('Planning Sheet Template'!C5,'Open Orders'!$F$6:$F$36000),MATCH('Planning Sheet Template'!$B2,'Open Orders'!$H$5:$EQ$5)))

The data is coming from a different sheet than where I'm entering the formula.

[/url][/list]

Looks OK.

Can you email the workbook?

 

[neeber34]

certainly... do you want to PM me your address?