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.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Customer name[/TD]
[TD]product number[/TD]
[TD]price[/TD]
[TD]qty[/TD]
[TD]ext. sale[/TD]
[TD]Category[/TD]
[/TR]
[TR]
[TD]ACE[/TD]
[TD]032-22-859[/TD]
[TD]$20.00[/TD]
[TD]100[/TD]
[TD]$2,000[/TD]
[TD]032-22[/TD]
[/TR]
[TR]
[TD]ACE[/TD]
[TD]032-22-789[/TD]
[TD]$18.00[/TD]
[TD]100[/TD]
[TD]$1,800[/TD]
[TD]032-22[/TD]
[/TR]
[TR]
[TD]ACE[/TD]
[TD]032-77-662[/TD]
[TD]$87.00[/TD]
[TD]100[/TD]
[TD]$8,700[/TD]
[TD]032-77[/TD]
[/TR]
[TR]
[TD]ACE[/TD]
[TD]032-77-491[/TD]
[TD]$82.00[/TD]
[TD]100[/TD]
[TD]$8,200[/TD]
[TD]032-77[/TD]
[/TR]
[TR]
[TD]ACE[/TD]
[TD]0044-56[/TD]
[TD]$79.00[/TD]
[TD]100[/TD]
[TD]$7,900[/TD]
[TD]0044[/TD]
[/TR]
[TR]
[TD]ACE[/TD]
[TD]0055-21[/TD]
[TD]$189.00[/TD]
[TD]100[/TD]
[TD]$18,900[/TD]
[TD]0055[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Billbur[/TD]
[TD]032-77-662[/TD]
[TD]$87.00[/TD]
[TD]100[/TD]
[TD]$8,700[/TD]
[TD]032-77[/TD]
[/TR]
[TR]
[TD]Billbur[/TD]
[TD]032-77-888[/TD]
[TD]$76.00[/TD]
[TD]100[/TD]
[TD]$7,600[/TD]
[TD]032-77[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cawlings[/TD]
[TD]032-22-859[/TD]
[TD]$20.00[/TD]
[TD]100[/TD]
[TD]$2,000[/TD]
[TD]032-22[/TD]
[/TR]
[TR]
[TD]Cawlings[/TD]
[TD]032-22-789[/TD]
[TD]$18.00[/TD]
[TD]100[/TD]
[TD]$1,800[/TD]
[TD]032-22[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]segment[/TD]
[TD]category[/TD]
[TD]March sales[/TD]
[/TR]
[TR]
[TD]ACE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]blades[/TD]
[TD]032-22[/TD]
[TD]formula needed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]032-77[/TD]
[TD]formula needed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]55-87[/TD]
[TD]formula needed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]55-84[/TD]
[TD]formula needed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]masks[/TD]
[TD]0044[/TD]
[TD]formula needed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0055[/TD]
[TD]formula needed[/TD]
[/TR]
</tbody>[/TABLE]

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.