Repair Order Sales Only with Tires Sales

Hello,

I'm trying to build a pivot table that returns the total repair order sales only if tires were sold on that repair order. I can create a pivot table that returns tire sales but I need help returning the additional repair sales on that repair order.

In the two repair order examples below, the pivot table would only return the total sales of $674.95 for repair order #2 because it has tire sales.

Example:
Repair Order #1
Oil Change $99.95
Wipers $19.95
Total $119.95

Repair Order #2
Oil Change $99.95
Tires $250.00
Brakes $325.00
Total $674.95

Thanks,
Daren

I think this is what you want. As often happens with pivot tables, sometimes you have to play with the source data to get what you want.

BTW: Thanks for using Excel Tables; they make life so much easier.

The key formula is in Column G, "Has Tire Sales" =ISNUMBER(MATCH("TIRES",IF([REPAIR ORDER]=[@[REPAIR ORDER]],[OPCODE],FALSE),0)). As usual, it pays to work the formula from the inside out.

IF([REPAIR ORDER]=[@[REPAIR ORDER]],[OPCODE],FALSE) this part of the formula establishes the range to look at. We re only interested in looking at rows that have the same Repair Oder as the current row. Other rows are not considered.

Then there is Match. We are matching the value "TIRES" against the "valid" rows established by the if statement. So for Repair Order 4500, that's rows 2:6. For Repair Order 4505, that's rows 7:9. Match itself returns the row on which the word "TIRES" is found, if it is found. If not, #N/A is returned. So the ISNUMBER converts this into True / False.

With the embedded if statement, this should have been an array formula. Somewhere along the line Microsoft changed that.

The other two columns just separate the charges in to Tire and Non-Tire. You might be able to set up a formula within the pivot table to do this, but I generally find it easier to do the heavy lifting outside the pivot table.