Vlookup

[Guest]

Option1

Why not sort the data and use the sub total option.

OPtion 2

Select the column containing the data you wish to
subtotal, select Filter Advanced filter,
copy data to other location and select unique records.

on this the use the sum if funtion on the data range.

Regards

John

 

[raji]

John,
I cannot use the option 1.. because, I need to add the values ( in Qty
column) under each category in Column 2 (steps). I think The option 1, simply
tells adds the number of entries under the sub heading. correct?
Second option, what should I use for critera range ?
Thanks for your response.
-raji

 

[Ken]

Looks to me like John's option one would work. The sub-
total function will count, add, average, etc. your first
column for each change in the second column, assuming that
you sort your second column as John suggests.

 

[Guest]

Advance filter options

select copy to another location
List range would be B1 to end of data in column B,
Criteria range, leave blank
Copy to another cell ie d1
Ensure unique records only is ticked

you would use then in e2, =SUMIF(B2:B5,D2,A2:A5) for
example.

By they way did you try the sum product option, I still
can't get my head around that one. Just waiting for the
penny to drop..

Regards

John

 

[raji]

Ken /John,
I used option 1, like this
=SUBTOTAL(9,A2:A9) on a table it gave me answer as 175 which is nothing but
the sum of all the wafer qty. I don't want that. I want it to add the qty
under each seperate steps. Am I using the function correctly? What is ref1..
29 mean in this actually?
-thanks
raji

 

[raji]

I got answer as 25 when I should get 75

Advance filter options

select copy to another location
List range would be B1 to end of data in column B,
Criteria range, leave blank
Copy to another cell ie d1
Ensure unique records only is ticked

you would use then in e2, =SUMIF(B2:B5,D2,A2:A5) for
example.

By they way did you try the sum product option, I still
can't get my head around that one. Just waiting for the
penny to drop..

Regards

John

 

[Ken]

In your example you have a header row, seven rows of data,
and two columns. Using those assumptions, highlight A1:B8.
Then select Data -> Sort...
In the Sort by box select "steps" then click "OK."
With your cursor in A1, go to Data -> Subtotals...
In the "At each change in:" box select "steps."
In the "Use function" box select "Sum."
In the "Add subtotal to" box select "Qty."
Click "OK."

This should give you what you want.