Countif multiple criteria

[park_bench]

Hello,

 

I think I read once that you can define a name for an array of terms and then call that list as criteria in a CountIf or SumIf formula but now I can't find any material on it.

 

So for example your list called 'colours' might be {"green", "yellow", "blue", "brown"}

 

and you could do something like: =countif(range, {colours})

 

Does anyone know if this is possible or was it a beautiful dream?

 

Thanks,

 

Ben

[apeo]

Think you need dcounta.

 

=dcounta(database,field,criteria)

[apeo]

Heres an Example:

 

Say your table is =

 

Colours

Brown

Green

Brown

Yellow

Yellow

Green

Blue

 

If you want to count how many Brown and Green then you need to create a criteria table =

Colours

Brown

Green

 

Your formula will look like this =

=dcounta(A1:A8,"Colours",A10:A12)

 

Your database table is in the range A1:A8 and your criteria table is in the range A10:A12

[park_bench]

Great thanks! I'll have a play with that.

 

Ben