Subtotal function

[Guest]

Hello,
Here is my worksheet:

a b c d
1 contract amount
2 m-7-001 1,000
3 Not Yet 2,000
4. m-7-005 4,000
-------------
Subtotal 7,000,-
=======
My question is how can we make it, to ONLY subtotal if the column b is not
"not Yet"
I now there is a function of sum if, but we prefer sbutotal, because if we
filter column b by contract no. the subtotal can work the filtered ones while
for sumif I do not think it works

Thanks in advance for any idea provided.

Frank

 

[Bob Phillips]

This will subtotal and take care of filtered rows

=SUMPRODUCT(SUBTOTAL(9,OFFSET(C1,ROW($C$1:$C$5)-ROW(C1),,1)),--($B$1:$B$5="Not
Yet"))

If you have Excel 2003, there are new values in SUBTOTAL to manage filtered
rows

=SUBTOTAL(109,C2:C5)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thank you Bob for your quick response. Since I am not too clear about the
offset, how if the subtotal is in the upper row, maybe with this I can
understand the offset
mechanism. I have ever used it but it is not too clear now.
Actually I will put the subtotal above.
So the layout is as follows;(lets say the row could be upto 500 rows)
a b c d
1 Subtotal 7000
2 contract amount
3 m-7-001 1,000
4 Not Yet 2,000
5. m-7-005 4,000

 

[Guest]

Hi Frank

If I understood this right, Col B is your list of contract names, one of the
values is "Not Yet",
A B C D
1 Contract Amount
2 M-30 7000
3 N-35 6000
4 Not Yet 7909

You can use the function, =SUBTOTAL(9,D57) at the top of D if you wish,
apply filter & use the custom setting of "where row B does not equal "not
Yet". your subtotal displays the sum of the other 2 rows , in this case
13000. Does this help? Please let me know

 

[Guest]

Sorry for a typo, in this case the formula will be =subtotal(9,D24)

 

[Bob Phillips]

Frank,

My formula assumes that you will be subtotalling column C, and that you are
testing against column B. If your data does not start in row 1, adjust all
references to row 1 to your start row.

So if you start in row 5, use

=SUMPRODUCT(SUBTOTAL(9,OFFSET(C5,ROW($C$5:$C$500)-ROW(C5),,1)),--($B$5:$B$500="Not
Yet"))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Dear Bob:

Thank you very much Bob. It works perfectly for me. You are so genius. I do
not understand fully on how the fomula is done, because very very
complicated, what I do is just copy the formula and ajust the column
parameter.

Again thank you very much.

Frank
Greeting from Jakarta, Indonesia.

 

[Guest]

Thank you NPM, Bob already told me the correct formula. I know how to make
subtotal, but what I want is withe the subtotal there is a kind of sumif. coz
I want the "Not Yet" excluded from the subtotal. When we want to filter the
specific contract name, the subtotal will show the filtered ones whileas
sumif does not have this capacity.

Thanks

Frank