Search code examples
excelpivot-tabledistinct-valuesunique-values

Sum unique values based on another column

I have four values: Name, Maturity, Hasinteracted?, SizeofName

https://i.sstatic.net/s7Czc.png

Entity (Constant) - represents the name of the entity.
Maturity (Variable) - In months, how long have they been active with us (i.e. 1 would be one month etc)
Hasinteracted? (True, False) - If yes, their value is TRUE
SizeofEntity (constant) - How large is this entity? This value varies for different Names but is constant for its' own name (i.e. Entity A will always be 12 regardless of their maturity, while Entity B will be 19 regardless of their maturity)

What I am trying to do:

Using a PivotTable, I have been able to create a table which has Maturity in the rows, and count of interactions for each maturity in the column.

The part that I am struggling with is the total SizeofEntity. Basically, I want it to sum the total SizeofEntity. The issue is that if Entity A repeats in the name section, it will double in this calculation.

Example

Entity A shows once (size = 12) in maturity 1 and maturity 2, Entity B shows once (size = 19) in maturity 1.

What I am hoping to see in the value section is the total SizeofEntity in Maturity 1 being 31 and 12 in Maturity 2, which occurs.

https://i.sstatic.net/3Mbcr.png

Now, if Entity A was to interact twice in Maturity 1, I would want the size to remain same, but the number of interactions to increase. With the way it is currently structured, because Entity A is repeated, the interaction now increases by one, but so does the size of the entity (from 31 to 43).

Is there a way to have Excel ignore the value in the SizeofEntity if the Entity name is repeated in another column, or is there another way to make this possible?

The data provided in the images is a small example. I am dealing with a much larger dataset with over 300 different names with hundreds of interactions per maturity period.

Solution

  • I've achieved your expected results using the following logic.

    Create a column called EntityConcat on column E. With this you can detect if you have duplicate entity and maturity pairs. I've concatented them using this formula then dragged down:

    =A2&B2

    In another column (F) detect if they repeat and only flag the first occurence:

    =IF(COUNTIF(E$2:E2,E2)=1,1,"")

    Finaly I've created another column that shows sizes for the first occurence only:

    =IF(F2=1,D2,0)

    Now on your pivot table you can count "SizeofEntity" for total number of occurences but should instead sum this last column's values, where no duplicate entity sizes will be found.

    Hope this is what you were after.