Can SUMPRODUCT change the range it uses?

joeq

Board Regular
Joined
May 31, 2003
Messages
109
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
 

Some videos you may like

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
Joined
Feb 13, 2004
Messages
4,814
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
Joined
Dec 20, 2005
Messages
12
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
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

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
Joined
Dec 20, 2005
Messages
12
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
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

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
Joined
Dec 20, 2005
Messages
12
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
Joined
Sep 3, 2002
Messages
10,473
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,055
Messages
5,569,946
Members
412,299
Latest member
agentless
Top