sum lookup based on 2 conditions

[chrisbogy]

Sorry to post such a stupid question but I just can't figure this out. I'm trying to sum sales data based on 2 criteria. Criteria 1: type of car, criteria 2: withing a price range and to make it more complex I have the information on 2 different sheets within 1 workbook. Sheet1 data dump looks like this:

[pre]

M           N           O             P      Q       R        S      T        U
Class	    Certified	Deleted Date	Status	Age	Body	Color	R/W	Price
Car, Sport		1/10/2013		29	2D Convertible	R	$9,000
Car, Compact		1/8/2013		8	4D Sedan	R	$9,999
Car, Compact		1/19/2013		51	5D Hatchback	R	$11,000
Car, Subcompact		1/4/2013		8	4D Hatchback	R	$11,339
Car, Luxury		1/19/2013		17	2D Convertible	R	$11,500
Sheet2  My table looks like this:

$0-$14,999   $15,000-$19,999   $20,000-$29,999  $30,000-$39,999   $40,000 +

Subcompact	$11,339	            $0	          $20,000           $0            $0
Compact	        $55,999	        $34,000	          $22,995	    $0	          $0
Intermediate	$28,305	         $83,887             $0	            $0            $0
Sport	        $18,000	           $0	           $50,499 	    $0	          $0
Luxury
Luxury Sport
Luxury High

[/pre]
I would like total the dollar amount based on vehicle "Class"(ie. Car, Subcompact) and price bucket(ie. 0-14,999). So the formula would need to search for all "Car, Subcompact" then search for the Price between 0-14,999 then total that amount.

It sound so easy in my head but I'm just not skilled enough to figure this out! I've read several posts about Array formulas but they quickly go over my head.


I hope it posts properly. Thanks for your help!

 

[jason]

this would be helpful to have a sample of your workbook for the rest of us to 'play with'


also,

how do you wish the criteria to be 'set'? are you doing to enter/type/select?

 

[kaushik03]

chrisbogy,


I agree with Jason regarding uploading a sample file.


But, meanwhile, did you try with SUMIF function?


Kaushik

 

[Luke M]

Hi chrisbogy,


You can do what you need using the SUMIFS function (if using 2007+), or SUMPRODUCT. To see SUMPRODUCT example, see here:

http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/


Would be something like:

=SUMPRODUCT((Range1=SpecificCar)*(PriceRange>=LowerValue)*(PriceRange<=UpperValue))

 

[chrisbogy]

All,


I wanted to upload the file in my original post but was unable to figure out how to attach a file, I don't see any options for attaching files.

I'm using Office 2007 and have tried the SUMIF function and the SUMPRODUCT function, however my formula looks different than Luke's example. I'll play with Luke's example and see if I can make it work. Again, sorry for my Excel ignorance and thanks for all the help! I really appreciate it!!


Chris

 

[SirJB7]

Hi, chrisbogy!

Give a look at the green sticky posts at this forums main page for uploading guidelines:

http://chandoo.org/forums/topic/posting-a-sample-workbook

Regards!