Conditional formula execution

[FlexicareTommy]

My companies reporting system gives the most garbage report for analyzing with excel. Rather than spending hours rearranging data to a more Excel friendly format (marco's won't work...I think), I'm wondering if there's an imbedded conditional formula to help out. I've tried a few different ideas, but can't seem to crack this puzzle. To give an overview of the objective, we're trying to create a report to view sales to a given customer by the product category instead lump sum total sales or even individual items. This will allow us to determine if a particular product line is growing or struggling.

The raw data that our inventory system generates looks like this. I used a vlookup to include the category column.

Customer name	product number	price	qty	ext. sale	Category
ACE	032-22-859	$20.00	100	$2,000	032-22
ACE	032-22-789	$18.00	100	$1,800	032-22
ACE	032-77-662	$87.00	100	$8,700	032-77
ACE	032-77-491	$82.00	100	$8,200	032-77
ACE	0044-56	$79.00	100	$7,900	0044
ACE	0055-21	$189.00	100	$18,900	0055
Billbur	032-77-662	$87.00	100	$8,700	032-77
Billbur	032-77-888	$76.00	100	$7,600	032-77
Cawlings	032-22-859	$20.00	100	$2,000	032-22
Cawlings	032-22-789	$18.00	100	$1,800	032-22

<tbody>
</tbody>

On our reports, we want to have one sheet per customer, something that would look like this:

Customer	segment	category	March sales
ACE
	blades	032-22	formula needed
		032-77	formula needed
		55-87	formula needed
		55-84	formula needed
	masks	0044	formula needed
		0055	formula needed

<tbody>
</tbody>

So essentially what I'm looking for is a kind of conditional sumif() statement. Searching the first column for customer name, if that matches, then performing a sumif() of all records with that customer so I can get the total category sales. Does anyone know a way I could achieve this?

 

[Brian from Maui]

If using 2003 or earlier try using SUMPRODUCT. For 2007 and later SUMIFS.

also look into using a Pivot Table

 

[xobile]

I agree with Brian. Use a Pivot Table.

Using Pivot Tables and Pivot Charts in Microsoft Excel

 

[dtplowry]

I agree Pivot tables seems to be the most likely option. You could then use the "show report filter pages" option for the different customers.