Count specific values in a range

[Wilco()]

I want to count a range (D12:NN12) in a sheet. In this row there can be values "L" or "XL".

I try to count these values by using countifs:

=COUNTIFS(D12:NN12;"XL";D12:NN12;"L")

But this gives me 0. I have thought of using Sumproduct, but cannot get that to work either.

How can I count the amount of cells containing these 2 values?

 

[Pierre]

Hello,

The COUNTIFS is not the best in your case. Try this one: =SUM(COUNTIF(D12:NN12,"L"),COUNTIF(D12:NN12,"XL"))

Pierre

 

[NARAYANK991]

Hi ,

Try this :

=SUM(COUNTIFS(D12:NN12,{"XL";"L"}))

Narayan

 

[Hui]

Wilco

Just so you know, your original formula wouldn't work because each criteria is additive

Using: =COUNTIFS(D12:NN12;"XL";D12:NN12;"L")
You are asking Excel which records in D12:NN12 match XL and which records in D12:NN12 match L

of course none match both criteria

You can use Countifs like
=COUNTIFS(D12:NN12;"L")+COUNTIFS(D12:NN12;"XL")

Most typically Countifs is used with multiple fields like
=Countifs(Field 1, Criteria 1, Field 2, Criteria 2, etc)

Where it is quite possible for a record to match both criteria at the same time and hence be counted.

You may want to have a read of:
http://chandoo.org/wp/2010/04/20/introduction-to-excel-sumifs-formula/