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

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

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

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

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

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

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

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