In a pivot table, return the value in one column (to be summed) if another column has a value in it

[tgappmayer]

Hey everyone! First post here, please forgive my ignorance.

I've got a pivot table I'm trying to build, and I need your help. Here is what my source data looks like:

Name	City	Like cats?	Like dogs?	Like snakes?	# Of Facebook Friends
John	Atlanta	Yes			300
Susie	Chicago	Yes	Yes		250
Mary	Chicago			Yes	700
Jack	Chicago		Yes		145
Joe	Seattle		Yes	Yes	400

<tbody>
</tbody>

In the above example, what I would like to do is end up with a pivot table that looks like this:

City	Likes Cats	Likes Dogs	Likes Snakes	Total	Total Unique
Chicago	250	395	700	1,345	1,095
Atlanta	300			300	300
Seattle		400	400	800	400

<tbody>
</tbody>

The two parts that I'm struggling with here are 1. counting '# of Facebook friends' if there is a value in columns B-D respectively, and then 2. Determining how to return a unique value to stop a particular row from being double or triple counted.

Thanks in advance for your help!

 

[Ruca13]

Add to the source data an auxiliary column with this formula:

=IF(C2<>"","Likes Cats",IF(D2<>"","Likes Dogs",IF(E2<>"","Likes Snakes","")))

Then on the pivot table, use that extra column in the column label, the City in the row label and the Sum of Facebook friends in Values.

I hope this is what you wanted.