Count multiple cells against multiple criteria in an Excel spreads

[EricB]

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

 

[Max]

Result1:
=SUMPRODUCT((C2:C10="Approved")*(D210="Product a"))

Result2:
Simplest to use autofilter.
Filter on col C for "Approved", get your results in cols A & B.

 

[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]

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.

 

[EricB]

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]

Something like this, I'd guess, is what you seek:
=SUMPRODUCT((C2:C100="Approved")*(D2100="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

 

[EricB]

Perfect

Thank you very much Max.

Regards

EricB

 

[Max]

Welcome, EricB. Glad it helped.