EXCEL Conditional Counting/Pivot Help

Microsoft Office 2007 professional - lic...
December 2, 2010 at 05:39:12
Specs: Windows 7
Hi,

I have a huge range of cells with names, parent skill, child skill, and department (like in the example below). I need to pivot off of this data so I can get a count of unique parent skills. For example, I want to know that the department EIS has 1 person with the parent skill System Admin. When I currently do the pivot it is counting Joe twice and giving me an answer of "2", but I need the answer to be "1". How can I accomplish this? Thanks in advance for any suggestions!


A B C D
NAME Parent Skill Child Skill DEPARTMENT

Joe System Admin Windows EIS
Joe System Admin Unix EIS
John App Dev C+ App
John App Dev COBOL App
Jim Networking Cisco EIG
Jim Networking Polycom EIG


See More: EXCEL Conditional Counting/Pivot Help

Report •


#1
December 2, 2010 at 07:21:16
First, you should read the How To referenced in my signature line to see how to post data so that it is easier for us to read:

    A         B              C              D
NAME   Parent Skill     Child Skill    DEPARTMENT

Joe     System Admin     Windows          EIS
Joe     System Admin     Unix             EIS 
John    App Dev          C+               App
John    App Dev          COBOL            App 
Jim     Networking       Cisco            EIG 
Jim     Networking       Polycom          EIG

Second, if you are using Pivot Tables, I can't help since I don't use them.

However, from a formula perspective, I can tell you this:

I don't think you can construct an Excel formula that checks for matching values in 2 columns but also checks to see if the corresponding cells in Column A are the same for more than one of those matches and then disregards all but one of them.

Excel formulas can't really do interative analysis like that. A User Defined Function written in VBA could probably do it, but no builit-in Excel function will do it without specifically checking the names. e.g. this will return 1, but it's only because we used Joe in the formula:

=IF(SUMPRODUCT((B1:B6="System Admin")*(D1:D6="EIS")*(A1:A6="Joe"))>1,1,
SUMPRODUCT((B1:B6="System Admin")*(D1:D6="EIS")))

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#2
December 2, 2010 at 11:46:42
Thanks for the tip and sorry for the unformatted post! I will try this solution tonight.

Report •

#3
December 4, 2010 at 05:29:39
Hello,

For easy implementation and flexibility, I wrote this code to allow unique count in pivot tables. It's just a code to paste. I' m using it and looking for testers.

http://lazyvba.blogspot.com/2010/11...
Let me know and have a good day



Report •

Related Solutions


Ask Question