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]

	A​	B​	C​
1​	1​		criteria
2​	0​		1​
3​	2​		0​
4​	3​
5​	4​		4​
6​	0​
7​	1​

<tbody>
</tbody>

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.