Issue dealing with simple excel issue - Formula and Pivot

hi joey. could you explain why "Customer BMSI -Biomedical Sciences Institute under ACAD" is counted as one? in your Raw worksheet, it appears in row 18, 19, 36 and 37. so it should be 4? if so, go to your PivotTable Field List and drag the Customer field to the VALUES area. since it's made up of text, the default will do a count of the non-empty rows.

i'm not really sure what you are trying to do. the PivotTable only shows what is inside your source data. so for eg, it does not have customer XYZ from Ind. hence, the PivotTable does not show that. whatever it shows, it will definitely be 1 count. so is there any difference what others should show?
BMSI -Biomedical Sciences Institute
BMSI -Biomedical Sciences Institute R
Nanyang Technological University
Nanyang Technological University-RRR
Singapore Polytechnic

the Customer field can be in both ROWS and VALUES area. i have uploaded an eg to show you. perhaps, you can show what are the desired answers you are hoping to see.

if you want all to show as 1 and the subtotal to show the unique customer it has under Ind, then it could be easier to repeat the Ind field name. i forgot the tab name it should have in Excel 2010. but click on the PivotTable and 2 extra tabs will appear on top. try looking out for Report Layout -> Repeat All Item Labels. i did it for you in a 2nd PivotTable.

one way
you can install PowerPivot then in PT dialog window tick Add to Data Model, and in Value Filed - set Customer to Distinct Count. After that set layout to Tabular Form

(I used benishiryo file)
hope you'll see PT on RAW sheet with Distinct Count

Oh my god, you are so good with excel, Guru!

Yes I tried the Repeat All Item Labels and it works. But how you managed to make the 2nd PivotTable appeared, within the same PivotTable sheet?

Your formula is so advance and complex. My desire outcomes already shown in your formula, which is... ACAD Total of 5, PHAR Total of 10, SI Total of 1 etc...

Is Pivot able to show this individual IND segment total?

So don't forget to install PowerQuery also These are two powerful things for excel
(or upgrade 2010 to 2016 - there is everything built-in)