Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
i have this kind of data
abc a single 213
abc b single 123
abc c single 123
abc d single 1
abc a multiple 123
abc b multiple 123
abc c multiple 123
abc d multiple 234
abc1 a single 213
abc1 b single 123
abc1 c single 123
abc1 d single 1
abc1 a multiple 123
abc1 b multiple 123
abc1 c multiple 123
abc1 d multiple 234
i want to get sum like this
abc a single 213
abc b single 123
abc c single 123
abc d single 1
sum 3465(whatever the sum is i just wrote random sum)
abc a multiple 123
abc b multiple 123
abc c multiple 123
abc d multiple 234
sum 3465(whatever the sum is i just wrote random sum)
abc1 a single 213
abc1 b single 123
abc1 c single 123
abc1 d single 1
sum 3465(whatever the sum is i just wrote random sum)
abc1 a multiple 123
abc1 b multiple 123
abc1 c multiple 123
abc1 d multiple 234
sum 3465(whatever the sum is i just wrote random sum)
how i get this
go for Pivot table
ok but in pivot i get data like this
col1 | col2 | col3 | col4 | sum(col4) |
abc | a | mutliple | 123 | 123 |
single | 213 | 213 | ||
b | mutliple | 123 | 123 | |
single | 123 | 123 | ||
c | mutliple | 123 | 123 | |
single | 123 | 123 | ||
d | mutliple | 234 | 234 | |
single | 1 | 1 | ||
abc1 | a | mutliple | 123 | 123 |
single | 213 | 213 | ||
b | mutliple | 123 | 123 | |
single | 123 | 123 | ||
c | mutliple | 123 | 123 | |
single | 123 | 123 | ||
d | mutliple | 234 | 234 | |
single | 1 | 1 |
but actually i want get data like this
single | ||
abc | a b c d | 213 |
123 | ||
123 | ||
1 | ||
Sum 457 | ||
multiple | ||
abc | a b c d | 123 |
123 | ||
123 | ||
234 | ||
Sum 603 | ||
single | ||
abc1 | a | 213 |
b | 213 | |
c | 123 | |
d | 1 | |
sum 547 | ||
multiple | ||
abc1 | a | 123 |
b | 123 | |
c | 123 | |
d | 234 | |
sum603 |
is this possible how to group by col4 as headers
May be like attached
Cross tables ..not sure though
Use pivot table add col1 and col 2 into Row and put sum(col4) in column area.you will get the same result.
The solution is to use TOTAL in your Sum expression. See attached application...
The expression should look like this:
Sum(TOTAL <col1,col3> col4)
This takes the sum of col4, but groups by col1 and col3. This will avoid showing the sum in terms of col2. Notice that the Totals at the top of the column is actually correct, not the sum of all the numbers below it!
Also, please note the sort order; I changed it to make the data look like your desired order.