Counting

[eng_jamal]

Hello I would ask you how can count many value in many column by using a conditions

 

[C with no eyes]

COUNTIF or COUNTIFS

 

[eng_jamal]

I used it but when countifs don't work
For example:
I have range for A to g cells how can count cells which contain 0 or 1 in the range

 

[C with no eyes]

=countif(a:g,0)+countif(a:g,1)

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]criteria[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

c5=SUMPRODUCT(COUNTIF(A1:A7,C2:C3))

 

[eng_jamal]

Thanks friends
I have another question if I have ex:
I have range of cells in many colunms and I want to count the cells which contain word "center" in range a2 to a100 and cells b to g which contain 0

 

[C with no eyes]

Your question is slightly ambiguous.

If you want to add number of cells containing center to number of all cells containing 0 use =COUNTIF(A2:A100,"*center*")+COUNTIF(B:G,0)

If you want to add all the cells that contain 0 if the respective row contains the word center use =COUNTIFS(A:A,"*center*",B:B,0)+COUNTIFS(A:A,"*center*",C:C,0)+COUNTIFS(A:A,"*center*",D:D,0)+COUNTIFS(A:A,"*center*",E:E,0)+COUNTIFS(A:A,"*center*",F:F,0)+COUNTIFS(A:A,"*center*",G:G,0)

It's a bit longwinded but gets the job done. It can be neater with an array formula but I tend to stay away from these as they have the capacity to cause problems if the spreadsheet is available to multiple users.

 

[eng_jamal]

Thanks a lot
I would like to know how can formula be if use array I want short formula
Thanks in advance

 

[Marcelo Branco]

Maybe this

=SUMPRODUCT(ISNUMBER(SEARCH("center",A2:A100))*(B2:G100=0)*(B2:G100<>""))

M.