Sum from multiple column

[pranamu]

Hello Expertise,

I need a favor from you. I have some data like below where a project is done by multiple vendor and their expenses is given in multiple column.
Project ABC is done by Santosh and Steph in first phase and for the same project Scott is also involved in 2nd phase

Project	v1 Name	v1 Value	v2 Name	v2 Value	v3 Name	v3 Value
ABC	Santosh	1500​	0​	0​	Steph	735​
ABC	Scott	1000​	Scott	1250​	0​	0​
ABC 1	Steph	300​	Steph	400​	Sharon	735​
ABC 1	Mike	750​	0​	0​	0​	0​
ABC 2	Sharon	825​	0​	0​	Scott	135​

Now I want to add all of these vendors cost in one row under their cell as per project. Let's say if any of below vendors are involved on the project their expenses should be added.

Project	Santosh	Scott	Steph	Mike	Sharon
ABC
ABC 1
ABC 2

Can someone please help me on this? any kind of formula will be helpful
Thanks you so much in advance.

 

[James006]

Hi,
Have you tried to insert a Pivot Table ?

 

[awoohaw]

@pranamu, what version of excel do you have? If you have 2010 or later you can unpivot your mixed crosstab into a flat file with Power Query and then make the pivot table as @James006 suggests. You would also be able to use formulas much easier with the flat file.

How big is your data?

 

[James006]

@awoohaw

You are 100% right ... !!!
Hopefully OP does know the difference between CrossTab and Flat File ...

For the OP :
A macro can very easily transform any crosstab worksheet into a proper DataBase
However, if you need a smart, simple solution without macros :

[SOLVED] Convert Cross Table to Flat List in Excel

Here's a quick and simple guide on how to convert cross table to flat list in Excel.

www.supereasy.com

 

[pranamu]

I haven't tried pivot yet, can I have some formula please. I want this to be done through formula instead pivot

 

[James006]

I haven't tried pivot yet, can I have some formula please. I want this to be done through formula instead pivot

As explained above ... there are two distinct questions by order of priority :

1. Your Data Structure

2. The Method to Analyze (Formula or Pivot)

 

[awoohaw]

@pranamu , you can't really pivot your data as it is because it is somewhat pivoted already. You need to get your data into only three columns first: Project, Name, Value. From their you can make a new pivot report or use formulas to present the data how you want.

 

[pranamu]

As explained above ... there are two distinct questions by order of priority :

1. Your Data Structure

2. The Method to Analyze (Formula or Pivot)

This is how I receive data from vendor on a daily basis and creating pivot on a daily basis will unnecessarily require to additional time. If I have formula to do so it will be easier for me, just dump the data daily and report is prepared.

 

[pranamu]

I

This is how I receive data from vendor on a daily basis and creating pivot on a daily basis will unnecessarily require to additional time. If I have formula to do so it will be easier for me, just dump the data daily and report is prepared.

tried using Pivot table and pivot chart wizard the project is being duplicated and vendor name too on the multiple row. So It's not helping me

 

[James006]

@pranamu , you can't really pivot your data as it is because it is somewhat pivoted already. You need to get your data into only three columns first: Project, Name, Value. From their you can make a new pivot report or use formulas to present the data how you want.

Do not forget to read all messages posted to your attention