Summing Column Values based on criteria

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?

Datasets

Input

Preparation

Accepted answers

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?

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?

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...

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:

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