More MVP Help Please!
MThompson
I'm using Excel 97
I've been to sites by JWalk, Chip Pearson, and Bullin (sp) and others, and
picked up some good advice!
I have tried DFunctions, Arrays, and Criteria & Extract like the 'old' (and
new) Excel 'Database' Extract, and don't want to go to a Pivot table. (I
finally broke down and did it with code, but I'd like to use a function if I
can)
Can this be done?
The 'List' is in A4:C8
I want to 'AutoFilter' the list for Job 222 then,
in cell C1 I'd like to know Job 222 'Amount' for its 'a' status
in cell C2 I'd like to know Job 222 'Amount' for its 'b' status
A B C
1 ?Formula?
2 ?Formula?
3
4 Status Job Amount
5 a 111 1
6 b 111 2
7 a 222 5
8 b 222 10
Thanks for all the previous help! You folks are great!
Mike Thompson mtho...@bellsouth.net
George Simms
I think I may have an answer, David Hager pointed out the solution, in his
news letter eee1.
http://www.j-walk.com/ss/excel/eee/eee001.txt
This solution uses a formula by Laurent Longre. (don't know *how* I
overlooked taking this one apart <g>)
Using your example. In B1 enter the letter a (Status), and in C1 this
*Array Formula.
=SUM((A5:A8=B1)*(SUBTOTAL(9,OFFSET(C5:C8,ROW(C5:C8)-MIN(ROW(C5:C8)),,1))))
Do the same in B2 b and *Array Formula in C2
=SUM((A5:A8=B2)*(SUBTOTAL(9,OFFSET(C5:C8,ROW(C5:C8)-MIN(ROW(C5:C8)),,1))))
*Array Formula Must be entered by holding down the Ctrl & Shift keys
then hit Enter. Do this after you select or edit the formula. Excel will
enclose the formula in { } if it's entered correctly.
The formula will recalculate with the visible filtered data .
Hope that solves the problem.
All the Best
George
Microsoft MVP - Excel
Newcastle upon Tyne
England.
MThompson wrote in message ...
MThompson
In my postings on the 13th I just wasn't explaining the situation well in
words.
Dave Hager pointed me to THE solution at Walkenback's site but after 2 hours
work I just couldn't make it work, and had to be spoon fed...Thanks again
George.
George Simms wrote in message ...