Do NOT count cell if adjacent cells have a 1

[abrig005]

Hello,
Each cell is either blank or has a 1.
I want to count the number of 1's only if there is not a 1 in any adjacent cell.
For example building 1 would only be counted under Class 2 and not 4 6 and 7
building 13 would only be counted under Class 1 and not Class 6 and 7
Class 1 is a simple count....
Class 2 would only count if there is not a 1 in the adjacent cell in Class 1 etc.

Thank you!!!!!!!!!!!!!!!

 

[jtakw]

Hi,

Is this what you mean:

Book3.xlsx
	A	B	C	D	E	F	G	H
1		1	2	4	6	7		Count
2	1		1	1	1	1		35
3	2			1	1	1
4	3				1	1
5	4				1	1
6	5				1	1
7	6				1	1
8	7				1	1
9	8				1	1
10	9				1	1
11	10				1	1
12	11				1	1
13	12				1	1
14	13	1			1	1
15	14				1	1
16	15				1	1
17	16	1			1	1
18	17				1	1
19	18				1	1
20	19					1
21	20					1
22	21					1
23	22					1
24	23					1
25	24					1
26	25					1
27	26					1
28	27					1
29	28					1
30	29					1
31	30					1
32	31					1
33	32					1
34	33					1
Sheet938

Cell Formulas
Range	Formula
H2	H2	=COUNT(B2:B34)+COUNTIFS(C2:F34,"1",B2:E34,"")

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1		1	2	4	6	7		Count
2	1		1	1	1	1		33
3	2			1	1	1
4	3				1	1
5	4				1	1
6	5				1	1
7	6				1	1
8	7				1	1
9	8				1	1
10	9				1	1
11	10				1	1
12	11				1	1
13	12				1	1
14	13	1			1	1
15	14				1	1
16	15				1	1
17	16	1			1	1
18	17				1	1
19	18				1	1
20	19					1
21	20					1
22	21					1
23	22					1
24	23					1
25	24					1
26	25					1
27	26					1
28	27					1
29	28					1
30	29					1
31	30					1
32	31					1
33	32					1
34	33					1
Lists

Cell Formulas
Range	Formula
H2	H2	=SUM(--(MMULT(--(B2:F34=1),{1;1;1;1;1})>0))

 

[abrig005]

Sorry for not being clear. I need to count all the 1's in Class 2 only if there isn't a 1 in Class 1, then Count all Class 4 only if there isn't a 1 in Class 2 or Class 1, then Count all Class 6 only if there isn't a 1 in Class 4 or Class 2 or Class 1, then Count all the 1's in Class 7 only if there isnt a 1 in Class 6 or Class 4 or Class 2 or Class 1

 

[Fluff]

So what you're saying is you want to count the total number of rows that have a 1 somewhere in them?
If so try the formula in post#3

 

[abrig005]

The results for the example above should be (note i have thousands of rows the example is just a subset)

Class 1 = 2
Class 2 = 1
Class 4 = 1
Class 6 = 14
Class 7 = 15

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[abrig005]

Version 2102 build 13801.21092

 

[Fluff]

That is the build not the Version of Excel which will be something like 2019, 2021, 365

 

[abrig005]

Office 365