comparing data and returning a subtotal

[arnold]

Please help. i have a set of TEXT in D120 that I will
like to compare with in A5. And if there is a match I
want all corresponding data (c1:c20) total hours in
B21. I have used this formula and it gives me the
corresponding hours of c1:c20. By comparing A5 to D120,
BUT it does not gives me the summary of all match hours.
Basically, there might be more than one corresponding
matches. This is the fomula i have used:
=INDEX(C1:C20,MATCH(A5,D120,0))
And this is an example of the date:
A1 B1 C1 D1
- 5.00 JOHN
- 1.00 PAUL
- 2.00 MARY
- 3.00 JOHN
- 1.00 LOVE
JOHN 8.00

Please i do not need the sum formula, because these data
changes regularly. Also, these data contain about 30500
cells. On a weekly bases i will auto filer the info. and
below the total i will insect a blank cell and enter
inform from D. CELL that i will like to track. As with
JOHN ON A5. i THINK I AM ON THE RIGHT TRACK BUT WANT THAT
FORMULA TO SUM TOTAL ALL CORRESPONDING HOURS in cell
C. :C20 THAT MATCHES IN CELL D. I am not familiar with
macro, may be this is what i need. please help
thanks a million

 

[Jason Morin]

You say you don't need a sum formula, but the way you
describe your problem, SUMIF is exactly what you need. In
B21:

=SUMIF(D120,A5,C1:C20)

HTH
Jason
Atlanta, GA

 

[Ken Wright]

Or the other option seeing as you are already autofiltering, might be to use the
SUBTOTAL function which will only sum the visible rows.

Related note for anybody with Excel 2003 that doesn't already know, SUBTOTAL now
has more optional arguments that allow you to sum visible rows only after
"hiding" rows as well as "filtering" rows.