# Can SUMPRODUCT change the range it uses?

#### joeq

##### Board Regular
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

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### acw

##### MrExcel MVP
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

##### MrExcel MVP
joeq said:
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

##### New Member
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

##### Legend

Book1
ABCDEFGH
1CarHouseLawnOtherLawn12/22/2005
212/20/20051234522
312/21/20053241
412/22/200555443322
512/22/2005123345456
612/22/2005
712/22/20053399
812/26/200511223212
912/27/2005
1012/28/20051111
11
Sheet1

#### neeber34

##### New Member
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

##### Legend

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

##### New Member
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

##### Legend
neeber34 said:
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?

Replies
9
Views
763
Replies
4
Views
382
Replies
3
Views
2K
Replies
2
Views
554
Replies
5
Views
947