Excel - Where is the condition?

Question

I have two columns with data:

AB 1 HELIOS AU 2 HELIOS 3 HELIOS AU 4 SPARTA 5 HELIOS AU Countif(A..;"HELIOS") AND Countif(B..;"AU")

I can count the number of HELIOS or SPARTA in a column. But how can I calculate the number of AUs on HELIOS or SPARTA?

+8

count excel where

Customx May 02, '11 at 14:28

source share

3 answers

Take a look at the sumproduct function

 =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 version of excel. :)

I think English is

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

+4

Nicola cossu May 02, '11 at 14:51

source share

DCOUNT should do what you are looking for.

However, this requires a shortcut string, but then it resolves things like this:

+3

jswolf19 May 02, '11 at 14:49

source share

kennytm · Accepted Answer · 2011-05-02T14:50:46+0000

Use COUNTIF S for counting with several criteria, for example.

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