Using SUMPRODUCT to show values like a pivot table

M

Mariux

New Member
Joined
Jan 5, 2013
Messages
4
I'd like to use the SUMPRODUCT (or other formula) with certain conditions to show results in the similar shape of a pivot table.
I have a database "clients, cost-centers, months, revenues". For some of the clients we may have multiple cost-centers. I need the formula to show revenues depending on : clients, cost-centers, months alltogether in the same table, eventually in the matrix on the below excel :

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Month_1[/TD]
[TD]Month_2[/TD]
[TD]Month_3[/TD]
[/TR]
[TR]
[TD]Client_1[/TD]
[TD]Cost_center1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Cost_center3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client_2[/TD]
[TD]Cost_center1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Cost_center2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Cost_center3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]





Thanks for your support!
Looking forward to hearing from you.
 
Sort by date Sort by votes
In the example below the formula in H2 is copied down and across:

Excel 2003
ABCDEFGHIJ
ClientCostCentreMonthRevenueMonth_1Month_2Month_3
Client_1Cost_center1Month_1Client_1Cost_center1
Client_1Cost_center3Month_1Client_1Cost_center3
Client_2Cost_center1Month_1Client_2Cost_center1
Client_2Cost_center2Month_1Client_2Cost_center2
Client_2Cost_center3Month_1Client_2Cost_center3
Client_1Cost_center1Month_2
Client_1Cost_center3Month_2
Client_2Cost_center1Month_2
Client_2Cost_center2Month_2
Client_2Cost_center3Month_2
Client_1Cost_center1Month_3
Client_1Cost_center3Month_3
Client_2Cost_center1Month_3
Client_2Cost_center2Month_3
Client_2Cost_center3Month_3

<COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0"><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]121[/TD]
[TD="align: right"][/TD]

[TD="align: right"]121[/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]281[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]602[/TD]
[TD="align: right"][/TD]

[TD="align: right"]602[/TD]
[TD="align: right"]161[/TD]
[TD="align: right"]764[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]502[/TD]
[TD="align: right"][/TD]

[TD="align: right"]502[/TD]
[TD="align: right"]82[/TD]
[TD="align: right"]224[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]702[/TD]
[TD="align: right"][/TD]

[TD="align: right"]702[/TD]
[TD="align: right"]906[/TD]
[TD="align: right"]157[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]245[/TD]
[TD="align: right"][/TD]

[TD="align: right"]245[/TD]
[TD="align: right"]922[/TD]
[TD="align: right"]327[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]121[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]161[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]82[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]906[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]922[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]281[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]764[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]224[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]157[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]327[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</TBODY>
Sheet2​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<THEAD>[TR="bgcolor: #e0e0f0"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</THEAD><TBODY>[TR]
[TH="width: 10, bgcolor: #e0e0f0"]H2[/TH]
[TD="align: left"]=SUMPRODUCT(--($A$2:$A$16=$F2),--($B$2:$B$16=$G2),--($C$2:$C$16=H$1),$D$2:$D$16)[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]
 
Upvote 0
Andrew Poulsom said:
In the example below the formula in H2 is copied down and across:

Excel 2003
ABCDEFGHIJ
ClientCostCentreMonthRevenueMonth_1Month_2Month_3
Client_1Cost_center1Month_1Client_1Cost_center1
Client_1Cost_center3Month_1Client_1Cost_center3
Client_2Cost_center1Month_1Client_2Cost_center1
Client_2Cost_center2Month_1Client_2Cost_center2
Client_2Cost_center3Month_1Client_2Cost_center3
Client_1Cost_center1Month_2
Client_1Cost_center3Month_2
Client_2Cost_center1Month_2
Client_2Cost_center2Month_2
Client_2Cost_center3Month_2
Client_1Cost_center1Month_3
Client_1Cost_center3Month_3
Client_2Cost_center1Month_3
Client_2Cost_center2Month_3
Client_2Cost_center3Month_3

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]121[/TD]
[TD="align: right"][/TD]

[TD="align: right"]121[/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]281[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]602[/TD]
[TD="align: right"][/TD]

[TD="align: right"]602[/TD]
[TD="align: right"]161[/TD]
[TD="align: right"]764[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]502[/TD]
[TD="align: right"][/TD]

[TD="align: right"]502[/TD]
[TD="align: right"]82[/TD]
[TD="align: right"]224[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]702[/TD]
[TD="align: right"][/TD]

[TD="align: right"]702[/TD]
[TD="align: right"]906[/TD]
[TD="align: right"]157[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]245[/TD]
[TD="align: right"][/TD]

[TD="align: right"]245[/TD]
[TD="align: right"]922[/TD]
[TD="align: right"]327[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]121[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]161[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]82[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]906[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]922[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]281[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]764[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]224[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]157[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]327[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #e0e0f0"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #e0e0f0"]H2[/TH]
[TD="align: left"]=SUMPRODUCT(--($A$2:$A$16=$F2),--($B$2:$B$16=$G2),--($C$2:$C$16=H$1),$D$2:$D$16)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Hi Andrew,

Thanks for your reply, it is useful. In my case, there are more clients than cost-centers (eg: 500 clients and 10 cost-centers) and for some of the clients we have several cost-center allocated. To me, it's more useful to have this done automatically. The matrix I've used is structured as follows :
VERTICALLY : copy-paste clients and cost-centers with "Remove Duplicates"
HORIZONTALLY : months

Basically, I need the last part that you indicated to show similar to a pivot, as shown below :

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Month1[/TD]
[TD]Month2[/TD]
[TD]Month3[/TD]
[/TR]
[TR]
[TD]Client_1[/TD]
[TD]Cost_center1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Cost_center2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Cost_center3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client_2[/TD]
[TD]Cost_center3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client_3[/TD]
[TD]Cost_center1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Cost_center3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
If you do not want to show the client in each row:

=SUMPRODUCT(--($A$2:$A$16=LOOKUP(REPT("z",255),$F$2:$F2)),--($B$2:$B$16=$G2),--($C$2:$C$16=H$1),$D$2:$D$16)
 
Upvote 0
Thanks, it works, but in my case, I have to consider a database with a large number of clients, and without pivoting this database I don't know which cost-center is allocated to which client. It is possible using SUMPRODUCT ?
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
How to pivot/summarize table of values by single row but multiple date columns?
Replies
0
Views
654
nshepo20
N
F
  • Question Question
Showing two types of financial data in the same pivot table
Replies
0
Views
380
futureexcelpro
F
S
  • Question Question
How to repeat a single value across Pivot Table columns
Replies
3
Views
2K
GlennUK
G
H
  • Question Question
Pivot: average of counts for multiple criteria
Replies
8
Views
2K
Aryatect
Aryatect
X
  • Question Question
Alternative for use of SUMPRODUCT or Pivot Table Distinct Count Function
Replies
1
Views
2K
Aladin Akyurek
Aladin Akyurek

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top