Need a count of items from different columns

[Sheela]

Hello,

My data is set up in columns eg, column XX, column BB, Column CC. and I have items in rows. Some rows are blank. I need to get count of each of the items from Column XX, Column BB and Column CC.
I used pivot table to do this, but in the PT field list, the fields are reported as XX, XX2, BB, BB2, CC, CC2 etc.

I did short pivot tables to get the count for items in XX, then in XX2. My data is in many many columns.. This is taking me very long time and I am sure there must be an efficient way of doing this calculation.

I have indicated a short list in the attached file.

I really appreciate your help.

Thanks
Sheela

 

[Khalid NGO]

Hello Sheela,
I am not sure I have understood you correctly. See the attached.

If you setup your data like option-2 in attached, formula will become easy to drag.

Regards,

 

[Sheela]

Thanks Khalid. It was just what I was looking for!!!
Sheela

 

[Khalid NGO]

Thanks Khalid. It was just what I was looking for!!!
Sheela

You are welcome.
Glad it solved.
Thanks for feedback.