Excel - Where condition?

Question

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?

Answer 1

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

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

Answer 2

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"))