Subtotal

[chipgreenfield]

Hey me again guys..

I am having trouble subtotaling filtered data. I am using the sumi
statement but that includes hidden numbers. Any help would b
appreciated.

Chi

 

[Paul B]

Chip, have a look at subtotals in help, for sum it wouls be like this
=SUBTOTAL(9,A2:A10)

--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **

 

[chipgreenfield]

Sorry..I should have explained better. I am trying use an if statemen
and subtotal at the same time. So basically instead of Sumif
optimally it would be SubtotalIf(I dont think thats an option)

So after I filter data, under certain conditions, it would subtota
only the stuff that is shown, not the hidden columns.

Ex. If i select Tom from the autofilter it should give me a subtotal o
each of his subject scores, not all the scores in the sheet. which i
what i get now using a Sumif statement.

Person Subject Score
Tom Math 100
Tom Math 80
Tom Math 90
Tom English 75
Tom English 100
Tom Science 100
Tom Science 8

 

[David]

Chip

Just use the SUMIF function and set -
Range: to be the Person range (with Tom, and presumably other names in as
well)
Criterion *: to be "Tom"
Sum_range: to be the Scores range

That should work, I hope

Regards

David

* My Excel says Criteria here, but there is only 1 criterion (singular) in
your case.

 

[Paul B]

Chip, that is what subtotal does, from help, "SUBTOTAL will ignore any
hidden rows that result from a list being filtered. This is important when
you want to subtotal only the visible data that results from a list that you
have filtered"

--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **

 

[chipgreenfield]

Paul,

I tried subtotal but this will add up all the scores. It does no
distinguish between math, english, and science. What I would like t
do is add a condition so it will subtotal each separately.

Chi

 

[Peo Sjoblom]

Describe how your table is setup, there are workarounds, for instance if you
filter data in
A2:A50 and you want to sum B2:B50 where the filtered data in A is math

=SUMPRODUCT(--($A$2:$A$50="math"),--(SUBTOTAL(3,OFFSET($B$2,ROW($B$2:$B$50)-
MIN(ROW($B$2:$B$50)),,))),$B$2:$B$50)

 

[Paul B]

Chip, filter on name and subject

--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **

 

[chipgreenfield]

Im not exactly sure how the sumproduct will work. My data is basically
like how I described below, except it continues for about 5000 lines
with hundreds of names under person. Basically I would just like to
select Toms name(or other students) and come up with his total points
for Math, English, and Science (there are a few more subjects as well)
w/o having to select each subject over again.

 

[chipgreenfield]

Maybe this might be a little easier. In the same example above. I a
trying to make a simple pie chart with the filtered data. However
when I use the chart template, instead of one "pie piece" each for th
total of each subject, it gives me seven different pieces for each lin
of data. I figured once I am able to add them up it will just char
the 3 subtotals for the 3 different subjects. Again any help will b
appreciated