|
Count multiple cells against multiple criteria in an Excel spreads
Hi
I am trying to count multiple cells against multiple criteria in an Excel spreadsheet: A B C D 1 Client a Underwriter a Approved Product a 2 Client b Underwriter b Declined Product b 3 Client c Underwriter c Approved Product a 4 Client d Underwriter d Approved Product b 5 Client e Underwriter e Declined Product a 6 Client f Underwriter f Declined Product a 7 Client g Underwriter g Approved Product a 8 Client h Underwriter h Approved Product a 9 Client I Underwriter I Approved Product b Two results are required: 1) Count Approved in column C if D = Product a (sum result = 4) 2) If C = Approved return data contained in cells A & B Any body? Please. Regards EricB |
Count multiple cells against multiple criteria in an Excel spreads
Result1:
=SUMPRODUCT((C2:C10="Approved")*(D2:D10="Product a")) Result2: Simplest to use autofilter. Filter on col C for "Approved", get your results in cols A & B. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "EricB" wrote: I am trying to count multiple cells against multiple criteria in an Excel spreadsheet: A B C D 1 Client a Underwriter a Approved Product a 2 Client b Underwriter b Declined Product b 3 Client c Underwriter c Approved Product a 4 Client d Underwriter d Approved Product b 5 Client e Underwriter e Declined Product a 6 Client f Underwriter f Declined Product a 7 Client g Underwriter g Approved Product a 8 Client h Underwriter h Approved Product a 9 Client I Underwriter I Approved Product b Two results are required: 1) Count Approved in column C if D = Product a (sum result = 4) 2) If C = Approved return data contained in cells A & B Any body? Please. Regards EricB |
Count multiple cells against multiple criteria in an Excel spr
Hi Max
I am getting a #NUM! error on the formula (?) Then with 'Result 2'. I understand autofilter is the easy way. We are however working with numerous stats of some 50.000 records per day. A formula (or two) will be appreciated. Regards Eric "Max" wrote: Result1: =SUMPRODUCT((C2:C10="Approved")*(D2:D10="Product a")) Result2: Simplest to use autofilter. Filter on col C for "Approved", get your results in cols A & B. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "EricB" wrote: I am trying to count multiple cells against multiple criteria in an Excel spreadsheet: A B C D 1 Client a Underwriter a Approved Product a 2 Client b Underwriter b Declined Product b 3 Client c Underwriter c Approved Product a 4 Client d Underwriter d Approved Product b 5 Client e Underwriter e Declined Product a 6 Client f Underwriter f Declined Product a 7 Client g Underwriter g Approved Product a 8 Client h Underwriter h Approved Product a 9 Client I Underwriter I Approved Product b Two results are required: 1) Count Approved in column C if D = Product a (sum result = 4) 2) If C = Approved return data contained in cells A & B Any body? Please. Regards EricB |
Count multiple cells against multiple criteria in an Excel spr
I am getting a #NUM! error on the formula (?)
Check/clear your cols C &/or D for any #NUM! error values (use autofilter to do this quickly). This is probably the root of the problem. As for 'Result 2', here's a simple non-array way to extract it dynamically in adjacent cols to the right of the source data. I'll assume source data in row 2 down, with the key col = col C (where you have the status, eg: Approved) Put in F2: =IF(C2="Approved",ROW(),"") Leave F1 blank. This is the criteria col. Put in G2: =IF(ROWS($1:1)COUNT($F:$F),"",INDEX(A:A,SMALL($F: $F,ROWS($1:1)))) Copy G2 to H2. Select F2:H2, copy down to cover the max expected extent of data in col C. Minimize/hide away col F. Cols G & H will auto-return only the lines in cols A and B with "Approved" in col C, with all results neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "EricB" wrote: Hi Max I am getting a #NUM! error on the formula (?) Then with 'Result 2'. I understand autofilter is the easy way. We are however working with numerous stats of some 50.000 records per day. A formula (or two) will be appreciated. Regards Eric |
Count multiple cells against multiple criteria in an Excel spr
Hi Max
Thank you, formula working fine now. ** If we assume that there are Values in Column 'E', how can I add these values using data from C & D?** i.e. C = Approved D = Product E = Value (Say $10.000) Result = $40.000 I hope this is the last on this subject from me. Kind regards EricB "Max" wrote: I am getting a #NUM! error on the formula (?) Check/clear your cols C &/or D for any #NUM! error values (use autofilter to do this quickly). This is probably the root of the problem. As for 'Result 2', here's a simple non-array way to extract it dynamically in adjacent cols to the right of the source data. I'll assume source data in row 2 down, with the key col = col C (where you have the status, eg: Approved) Put in F2: =IF(C2="Approved",ROW(),"") Leave F1 blank. This is the criteria col. Put in G2: =IF(ROWS($1:1)COUNT($F:$F),"",INDEX(A:A,SMALL($F: $F,ROWS($1:1)))) Copy G2 to H2. Select F2:H2, copy down to cover the max expected extent of data in col C. Minimize/hide away col F. Cols G & H will auto-return only the lines in cols A and B with "Approved" in col C, with all results neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "EricB" wrote: Hi Max I am getting a #NUM! error on the formula (?) Then with 'Result 2'. I understand autofilter is the easy way. We are however working with numerous stats of some 50.000 records per day. A formula (or two) will be appreciated. Regards Eric |
Count multiple cells against multiple criteria in an Excel spr
Something like this, I'd guess, is what you seek:
=SUMPRODUCT((C2:C100="Approved")*(D2:D100="Product A"),E2:E100) which returns the sum from col E for "Approved" in col C and "Product A" in col D Do take a moment to press the "Yes" button below -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "EricB" wrote: Hi Max Thank you, formula working fine now. ** If we assume that there are Values in Column 'E', how can I add these values using data from C & D?** i.e. C = Approved D = Product E = Value (Say $10.000) Result = $40.000 I hope this is the last on this subject from me. Kind regards EricB |
Count multiple cells against multiple criteria in an Excel spr
Perfect
Thank you very much Max. Regards EricB "Max" wrote: Something like this, I'd guess, is what you seek: =SUMPRODUCT((C2:C100="Approved")*(D2:D100="Product A"),E2:E100) which returns the sum from col E for "Approved" in col C and "Product A" in col D Do take a moment to press the "Yes" button below -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "EricB" wrote: Hi Max Thank you, formula working fine now. ** If we assume that there are Values in Column 'E', how can I add these values using data from C & D?** i.e. C = Approved D = Product E = Value (Say $10.000) Result = $40.000 I hope this is the last on this subject from me. Kind regards EricB |
Count multiple cells against multiple criteria in an Excel spr
Welcome, EricB. Glad it helped.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "EricB" wrote in message ... Perfect Thank you very much Max. Regards EricB |
| All times are GMT +1. The time now is 09:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com