Filtering a value out and keeping track of it separately

Hello,
Attached is a Pivot table (done on MAC).
This is a table with > 100K rows. Each row has a number under columns ABCD&E. For the most part, each row will contain only a number under one column. But occasionally it may contain values in 2 or more columns like that shown in the 2nd row #48145. For this row, columns B and E both have values of 3 and 1 totalling 4.

At the end of the table, the totals for ABCD&E are provided as well. But if there are any rows with greater than 1 value (like that in the 2nd row), then that needs to be subtracted from the table. That total (in this case 4) must be counted in a separate placeholder for ABCD&E. In this case B will have 3 and E will have 1. They will be separate running totals.

Is it possible to run another pivot on this? If not is there a simple formula to achieve this?
many thanks
-----------------
Pivot PNG.png

Could you attach a small sample worksheet (just what you have here will probably be enough), but show (manually) what you are expecting to see.

It's hard to tell which columns the numbers are in, and I'm not sure where you want your "separate placeholder". Also do you just want the total (e.g. 4) in that cell, or do you want to store the individual numbers?

To upload a workbook, click on Go Advanced, scroll down to Manage Attachments - click there and you will be able to browse for then upload your file.

Hi,

I find this very hard to understand.

Please put together a small subset of data, sufficient to cover all permutations. Then manually create a range of cells showing exactly what results you want to see. Leave us to decide whether a Pivot Table is necessary.

Originally Posted by amt7565

Thanks Shirleyxls. I realized that the format I posted was not good so pasted an image. Hope this helps?

Hi,

Pictures are rarely much use. Please upload an actual (sample) workbook. We prefer not to have to recreate data when you already have a workbook

Hi,

This is still confusing.

You say the file you uploaded is an example of a Pivot table that you want but you don't show what the data looks like. Unless we can see the data we can't decide whether it's in the right format to produce a PT.

Furthermore in past #7 you refer to row A1:E1 which is blank, and then refer to row 2 which contains column labels.
I assume you really mean Cells D3:E3 and D4:E4 but in all these things being clear is sort of important.

Upload the workbook with the original data so that we can take another look. In addition show what the pivot table should look like given that you seem to want to exclude rows where the count of values is >1. Finally what's important. The Overall total or the totals of individual columns or do you want both?