subtotal function using criteria

[dbroc]

I'm trying to use the subtotal function to total figures in a list based on a
technician's name. For example, use the subtotal function instead of the
sumif function. In short, can you use criteria with the subtotal function...

 

[Domenic]

Assuming that A2:A100 contains the name, and B2:B100 contains the
figures, try...

=SUMPRODUCT(SUBTOTAL(9,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(A2:A1
00="Name"))

Hope this helps!

 

[Bob Phillips]

Here is an example of a SUBTOTAL count with a criteria

=SUMPRODUCT(SUBTOTAL(3,OFFSET(C1,ROW($C$2:$C$19)-ROW($C$1),,1)),--($C$2:$C$19="Assigned"))


--
---
HTH

Bob


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

 

[Rajesh Kodethoor]

I am using a file with more than 10000 lines, i have used subtotal function &
when I remove the subtoal its taking a lot time to remove, is there any
solution to this?

 

[Dave Peterson]

You may want to consider using pivottables.

But I'd try turning calculation to manual, then remove the subtotals, then turn
it back to what it was (automatic??).

In xl2003 menus:
tools|options|calculation tab
is where you'd find this setting.

 

[Ashish Mathur]

Hi,

Just a clarification - have you used Data > Subtotal or =subtotal(9,range)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com