Hi Vasu,

I have 40,000 rows populated as below. as you can see, instead of having multiple rows for the same combination of {plant, state, fuel type and mover type) , i could potentially have one row for each unique combination of parameters making it less daunting to look at. more importantly, it can help me to look at the data from a plant perspective and not from a Unit perspective which is important for multiple reasons.

what follows is an example of what i have and what i am trying to arrive at. i would like to use excel formulae to automate process and anaylse the data further.

f i have data in the following format:

Plant Name	State	Unit ID	Fuel Type	Mover Type	Quantity
Glen	NC	8732	Coal	ST	10
Glen	NC	8733	Coal	ST	12
Glen	NC	8734	Coal	ST	14
Glen	NC	8735	Water	HT	11
Glen	NC	8736	Water	HT	18
Glen	NC	8737	Water	HT	40
Glen	NC	8738	Gas	CC	150
Glen	NC	8739	Gas	CC	10
Glen	NC	8740	Gas	GT	65
Glen	NC	8741	Gas	GT	121
Anchor	CA	4501	Coal	ST	9
Anchor	CA	4502	Coal	ST	11
Anchor	CA	4503	Coal	ST	13
Anchor	CA	4504	Water	HT	10
Anchor	CA	4505	Water	HT	17
Anchor	CA	4506	Water	HT	39
Anchor	CA	4507	Gas	CC	149
Anchor	CA	4508	Gas	CC	9
Anchor	CA	4509	Gas	GT	64
Anchor	CA	4510	Gas	GT	120

As we can see, the only Unique thing here is the UNIT ID Number.

I want to have a solution where i can have one line for each unique combination ( Plant, State, Fuel type, Mover type being ONE combination) and the resultant total Quantity for that combination. e.g.

is the solution for Plant "Glen". the summation is simply the {=sum(if((.........),..)} array formula, so thats not an issue.

the big part is creating a formula where the each unique combination can be displayed in a row.

on another note, what if there are weights involved for each of the Question Table's Rows. how do i do a weighted average quantity given the requirements are the same( the sum gets replaced by wt avg quantity)

Thanks

Kanwaljeet

Hey Ashish,

Thanks for the help. Greatly Appreciate it.

Kanwaljeet