Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate PivotTable Item Totals Based On Cell Contents?
Hi all - I've received some terrific info here before, and after 2 days
of struggling have decided to try again. Here's my problem: I have a PivotTable tied to an ODBC data source (AS400 Table). The data is updated daily. A simplified version of the table is: A B C D E 1 Sales Acct Dr Item Catg 2 Bob 100 Dr. A Zig Res 3 Bob 100 Dr. A Zag Res 4 John 101 Dr. B Zig Res 5 Tim 121 Dr. C Zog Ox 6 Tim 121 Dr. C Zag Res 7 Bob 133 Dr. D Zog Ox 8 Tim 141 Dr. E Zig Res 9 John 167 Dr. G Zog Ox 10 John 167 Dr. G Zag Res My PivotTable appears as follows: Sales Dr Acct Catg Item Total Bob 3 Dr. A 2 100 2 Res 2 Zig 1 Zag 1 Dr. D 1 133 1 Ox 1 Zog 1 John 3 Dr. B 1 101 1 Res 1 Zig 1 Dr. G 2 167 2 Ox 1 Zog 1 Res 1 Zag 1 (I hope that doesn't look to confusing). What I need is to calculate - based on the PivotTable data - how many of each category (Res, Ox) each sales person (Bob, John) has sold. I also need to exclude items that aren't tracked (Zig's). The sales manager viewing the spreadsheet needs to see all of the sales, regardless of whether the item is being tracked. So, in the above example Bob would have 1 "Res" sale and 1 "Ox" sale. John would also have1 "Res" sale and 1 "Ox" sale, because "Zigs" aren't tracked. If this makes sense to anyone, I'd appreciate any input you could give. Thanks in advance. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I conditionally delete rows based on cell contents? | Excel Worksheet Functions | |||
Finding cell contents based on certain criteria | Excel Discussion (Misc queries) | |||
Finding cell contents based on certain criteria | Excel Discussion (Misc queries) | |||
Reference Data Range based on cell contents | Charts and Charting in Excel | |||
How easy is it to loop in VBA based on cell contents? | Excel Worksheet Functions |