Extracting data

[ohmymy]

Hi,

If I have the following data:

Fruits Day 1 Day 2 Day 3 Day 4
Apple 1 3 4 5
Orange 1 8 2 6
Pear 1 9 2 6
Apple 2 6 3 8

What can I use to extract for example only the info on "apple", wit
summations, on another worksheet with the result looking like this:

Fruits Day 1 Day 2 Day 3 Day 4
Apple 1 3 4 5
Apple 2 6 3 8
Total 3 9 7 13

Tried using pivot table, but could not get it to sum across th
columns.

Anyone got any ideas?

Thx in advance

 

[ohmymy]

pls ignore my question, i know what to do already

 

[Peo Sjoblom]

I am sure you could use a pivot table to get the same numbers albeit maybe
not looking exactly
like in your example. However using an advance filter can do that, Assume
the example table
is in A4:E8. In H1 put Fruits and in H2 put Apple. Now start in the sheet
where you want it, do
data>filter> advanced filter, sekect copy to another location, in the List
range click the sheet where
the table is and select the range, e.g. Sheet1!$A$4:$E$8, click in the
criteria box and select the sheet tab againg and this time select
Sheet1!$H$1:$H$2, click in the copy to box and select the left uppermost
cell
where you want it. e.g. Sheet3!$A$17. Click OK

That would give you

Fruits Day 1 Day 2 Day 3 Day 4
Apple 1 3 4 5
Apple 2 6 3 8


now type total unde Apple, go to the next cell to the right of total and
press Alt + =
press enter, drag copy the formula across

Having said that I still think a pivot would be more powerful to use or even
the autofilter

 

[CLR]

I suggest looking at Data > Subtotals........on my sample it seemed to give
everything you're looking for, plus it gave the same info on all the other
fruits at the same time too......

Vaya con Dios,
Chuck, CABGx3

 

[ohmymy]

thx for all your suggestions! i m going to use pivot table to do it a
my data will be updated from time to time thus a pivot table suits i
best