CODEWITHSUNDEEP
excelcountwhere-clause
CustomX
CustomX

Reputation: 10113

Excel - Where condition?

I have two columns with data:

    A         B
1   HELIOS    AU
2   HELIOS
3   HELIOS    AU
4   SPARTA
5   HELIOS    AU

Countif(A..;"HELIOS") AND Countif(B..;"AU")

I can count the amount of HELIOS or SPARTA in a column. But how can I count the amount of AU per HELIOS or SPARTA?

Upvotes: 9

Views: 32017

Answers (3)

jswolf19
jswolf19

Reputation: 2303

DCOUNT should do what you're looking for.

It does require a label row, however, but then it allows stuff like this: enter image description here

Upvotes: 3

Nicola Cossu
Nicola Cossu

Reputation: 56397

Take a look at sumproduct function

http://www.meadinkent.co.uk/xlsumproduct.htm

=MATR.SOMMA.PRODOTTO((A1:A5="HELIOS")*(B1:B5="AU"))
=MATR.SOMMA.PRODOTTO((A1:A5="SPARTA")*(B1:B5="AU"))

These are the functions for my italian excel version. :)

I think that english one is

=SUMPRODUCT((A1:A5="HELIOS")*(B1:B5="AU"))
=SUMPRODUCT((A1:A5="SPARTA")*(B1:B5="AU"))

Upvotes: 4

kennytm
kennytm

Reputation: 523494

Use COUNTIFS to count with multiple criteria, e.g. 

=COUNTIFS(A:A, "SPARTA", B:B, "AU")

enter image description here

Upvotes: 19

Related Questions