# Count multiple cells against multiple criteria in an Excel spreads

E

#### EricB

Hi

I am trying to count multiple cells against multiple criteria in an Excel

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â€™

Regards

EricB

M

#### 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.

E

#### 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

M

#### 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.

E

#### 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

M

#### 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

E

#### EricB

Perfect

Thank you very much Max.

Regards

EricB

M