Logic/Excel Formula question

[scm160]

Hi Guys,

Currently creating a report for stakeholders in which we evaluate data for clients.

We currently present our data as rows and columns where data is filled based on client groups. Each record has a client ID we use to distinguish the client. We got a request for our report to present our data with subtotals for each client in the rows and columns.

For example:

Methods Value
Client1 A 5
Client1 B 10
Client1 C 15
Client2 A 5
Client2 A 5
Client2 A 5
Client3 A 5
Client3 B 10
Client3 B 10

Is there anyway to present this data using rows and columns for each client total? Ideally I was thinking of creating another column called client total but I don't think that is right....

Thanks for any help you guys have!

 

[63falcondude]

It's hard to tell from the sample data, are there 3 columns or 2? Is this what you're looking for?

Excel 2010
	A	B	C	D
1	Client	Methods	Value	Total
2	Client1	A	5	30
3	Client1	B	10	30
4	Client1	C	15	30
5	Client2	A	5	15
6	Client2	A	5	15
7	Client2	A	5	15
8	Client3	A	5	25
9	Client3	B	10	25
10	Client3	B	10	25
Sheet1

Cell Formulas
Range	Formula
D2	=SUMIFS(C:C,A:A,A2)

A pivot table would work well here too.

 

[scm160]

It's hard to tell from the sample data, are there 3 columns or 2? Is this what you're looking for?

Excel 2010

	A	B	C	D

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Client[/TD]
[TD="align: center"]Methods[/TD]
[TD="align: center"]Value[/TD]
[TD="align: center"]Total[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Client1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]30[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]Client1[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]30[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]Client1[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]30[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]Client2[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]15[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]Client2[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]15[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]Client2[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]15[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]Client3[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]25[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]Client3[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]25[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]Client3[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]25[/TD]

</tbody>

Sheet1​

[TABLE="******* 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="******* 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="******* 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="******* 10, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=SUMIFS(C:C,A:A,A2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



A pivot table would work well here too.

Sorry the data was presented like that didn't realize you could add in tables....I think using Sumifs will work in this scenario....

Regards,

scm

 

[63falcondude]

Glad to help.