Summing Column Values based on criteria

[ljohn]

Hello,

The way my data is structured, its proving challenging to transpose and add.

This is what my data looks like -- Based on multiple criteria: Name and 'P' number, I need to get the sum of Counts.

So the output would look like

Anyway I can manipulate this data and get the required output?

[JosephSerpis · Accepted solution]

Hi @ljohn  I mocked up a workflow that nearly matches your output. The only values that doesn't match is Mango P1. Let me know what you think?

[ljohn]

Thank you for the quick response!

I am trying it out with my dataset. Its a pretty huge dataset & giving me some errors.

More to follow...

[ljohn]

Thank you very much! I think I am almost there but your solution definitely answers the question I had. 

[ljohn]

I am unable to view the data in a presentable format given the amount of information and the 'Null cells'

Tried transposing the data in order to show only values that have data -- 

For Example:

	Top Store 1	Count	Top Store 3	Count	Top Store 3	Count
Mango	P1	11	P2	12	P3	5

notice that Mango doesnt show P4 becuase it is Zero

I used a Summarize tool after the Join which gives me:

Mango	P1	11
Mango	P2	12
Mango	P3	5
Apple	P1	3
Apple	P2	12
Apple	P3	5
Apple	P4	8

Now this just needs to be transposed to look like the example above...