Excel pivot table question - HotUKDeals
We use cookie files to improve site functionality and personalisation. By continuing to use HotUKDeals, you accept our cookie and privacy policy.
Get the HotUKDeals app free at Google Play

Search Error

An error occurred when searching, please try again!

Login / Sign UpSubmit

Excel pivot table question

£0.00 @
Hi, First off, apologies for asking this on HUKD, I know some get annoyed by non-deal related questions, but I kn0w there are a lot of technical people on here too. I'm trying to set up a pivot … Read More
Evilmonkey Avatar
9m, 3w agoPosted 9 months, 3 weeks ago
Hi,

First off, apologies for asking this on HUKD, I know some get annoyed by non-deal related questions, but I kn0w there are a lot of technical people on here too.

I'm trying to set up a pivot table and am struggling to get what I want out of it. Ideally I want a pivot table with a predefined row data, more details below

I have a couple of hundred rows and each detailing an event. There are 6 columns which each describe part of that event (say part a-f). All the data in all of these columns is one of 7 numbers (lets say 1 - 7). I want a table to be able to count the instances of each number in all of the columns.

So something like:-

a b c d e f

1 1 1 2 3 5

1 2 3 3 3 5

2 2 2 3 3 4

2 3 1 4 5 5

Would go to:-

# a b c d e f

1 2 1 2 0 0 0

2 2 2 1 1 0 0

3 0 1 1 2 3 0

4 0 0 0 1 0 1

5 0 0 0 0 1 3

I know I can do this on a countif, but there are another bunch of columns I need to have as filters (again, I know I can use countifs, but was hoping to avoid given the number of filters I need...)

Thanks for any tips!

E.M.
Tags:
Evilmonkey Avatar
9m, 3w agoPosted 9 months, 3 weeks ago
Options
Best Answer
try Mr excel or stack overflow.. pivots do have calculated fields that might do what you want off top of my head..

All Responses

(2) Jump to unreadPost an answer
Responses/page:
#1
try Mr excel or stack overflow.. pivots do have calculated fields that might do what you want off top of my head..
#2
Ta,

I'd forgotten about stack overflow. I tracked down the exact same thing I was looking to do - turns out it can't be done...

Saved me an hour or so of fruitless trying though!

Post an Answer

You don't need an account to leave a response. Just enter your email address. We'll keep it private.

...OR log in with your social account

...OR comment using your social account

Thanks for your comment! Keep it up!
We just need to have a quick look and it will be live soon.
The community is happy to hear your opinion! Keep contributing!