Getting unique list and amount sum by unique list wise - Reg.

with a pivot table.

and prefer formula.

1) You did not add that in your question.

2) Please also add the earlier question solved, if they are solved.

If they are not solve, i advice you to learn from that question, before adding an new one.

You say this in your workbook:

1. I want to generate automatic summary report for data extraction report only(data set sheet -N: X column),
2. Summary report not needed for master data (Data set A: L Column)
3. (In summary "A" Column unique list of services should come automatically in alphabetical order from the data extraction report "Data set -V" column.
4. Summary report sheet is a dynamic sheet, Extraction report (Data set N:X column) should equal to summary report data .
5. Summary report data has been manually calculated for a reference and model

1.-4. Several of the formulae you have been provided with over the past few days show how to extract a unique list from another one - in theory you have enough examples available to you to try to create this formula for yourself. You say you want to learn, so I assume you have tried: how far did you get? What formula did you try?
5. Once you have brought your data across, you can simply apply a SUMIF function to work out the calculation.

Please try to have a go yourself - it will help you to understand better how these things work. This is how I have learnt - trial and error.

OK, so please attach a copy of the workbook with your attempted formulae in place.

e3 =SUMIFS('Data set'!$Y$10:$Y$149,'Data set'!$V$10:$V$149,C3,'Data set'!$R$10:$R$149,E2)

I never prefer pivot table.

I've seen this statement in your threat a couple of times.

But you tell us you want to learn, and pivot table is a very powerfull tool in excel, which Microsoft expands in every new version of Excel.
That is the way Microsoft is telling, that is the way to go (to my opinion).

So instead of neglecting the solutions with pivot table, you should embrace them and try them yourself.

Once you know the basics of it, I bet you gonna love the power of pivot table.

What is the reason not mark this one solved.

http://www.excelforum.com/excel-form...n-differs.html

you formula

Please Login or Register  to view this content.

The dataset starts at column N and not column I.

What is the reason not mark this one solved.

http://www.excelforum.com/excel-form...n-differs.html

http://www.excelforum.com/showthread.php?t=1153517

Please see.. marked as "SOLVED"

thanks

That is a differant question.

If you trying to be funny, I can't laugh with this one.

Logics is different oeldere... here i want to extract only the unique list of service. based on unique list i will apply sumif function. So, hope "N" will not apply here.

You get 2 list in your file, 1 you don't want to use and 1 you want to use.

You will do yourself a favour if you don't add in the file, what you don't need. That is also easier for the forummembers to understand your problem.

Your range of the data you want to use is N - Y, in that case there can not be a part of the formula, refering to column I.

The concept is very simple.

1) if so, you can solve this concept yourself.
but probably it is more difficult since you asking for help.

2) I have no questions.

I showed you that in the last posted file, you have failure in the formula.

3) please will you also mark the other answered questions to solved.

This is the correct formula:

Please Login or Register  to view this content.

However, it returns blank because there are some blanks in the range I:I - if you add a value to the blank cells, it works. In other words, your dataset in column I needs to be complete for it to work. It will not work if there are blanks in that column.

@AliGW

I think it should take the data from the 2nd table, according to the request in #1 of the OP.

2. my requirement is in the 2nd sheet only. i.e., summary report table. I want automatic summary table for data extraction report only, not for master data set.

In that case I don't expect a column I in the formula.

No, he wants a summary of the table on the left - trust me.

But then again ... maybe not? LOL!

@AliGW

I trust you, but that is NOT how I read the question!!!

Unless i can not open and reply again.

Yes you can, in the same options where you mark your question solved.

Please also reply on my sumif formula in #9.

The -2 takes 2 off of the count of that column (we don't want to count the headers).

Originally Posted by oeldere

@AliGW

I trust you, but that is NOT how I read the question!!!

Yes, on re-reading it, I completely agree, but confusion abounds, it seems!

It should be -2 really, but -5 seems to work as well. It has nothing to do with columns: it is taking into account headings at the top of your column V.

Now, here's the thing: you need to use the second formula if you are wanting to take data from the extraction list, and the first if you are taking the list from the master data list. You MUST understand that the column you are referencing in the array formula MUST be within the table you want to get the data from. Do you understand?

I suggest you go away now and have a really good play with the formulae you have. You should not come back so quickly with your next problem: you need to give yourself longer to puzzle it out.