SUMIFS or Sumproduct Help

[engineer33]

Hi Guys,

I need a formula that sums the equipment for each zone. Formula that works even if i change the equipment names to other columns i still obtain the same result by using the below table

ZONE 1
PX: 0
Signals:2
Tpws: 2


ZONE 2
PX: 1
Signals:3
Tpws: 3


TABLE 1.

Row/Column</SPAN>	A</SPAN>	B</SPAN>	C</SPAN>	D</SPAN>	E</SPAN>	F</SPAN>
1</SPAN>	Zones</SPAN>	PX</SPAN>	Zones</SPAN>	Signals</SPAN>	Zones</SPAN>	TPWS</SPAN>
2</SPAN>	2</SPAN>	Sydney street- PX</SPAN>	1</SPAN>	SUN909</SPAN>	1</SPAN>	SUN909</SPAN>
3</SPAN>	3</SPAN>	Adelaid Street- PX</SPAN>	1</SPAN>	SUN914</SPAN>	1</SPAN>	SUN914</SPAN>
4</SPAN>	3</SPAN>	Young St.- PX</SPAN>	2</SPAN>	SUN910 </SPAN>	2</SPAN>	SUN910 </SPAN>
5</SPAN>	4</SPAN>	Tower st- PX</SPAN>	2</SPAN>	SUN906</SPAN>	2</SPAN>	SUN906</SPAN>
6</SPAN>	4</SPAN>	Ardeer Station- PX</SPAN>	2</SPAN>	MW131</SPAN>	2</SPAN>	MW131</SPAN>
7</SPAN>	5</SPAN>	West Street-PX</SPAN>	3</SPAN>	SUN901G</SPAN>	3</SPAN>	SUN901</SPAN>
8</SPAN>	5</SPAN>	Fitzgerald-PX</SPAN>	3</SPAN>	SUN900G</SPAN>	3</SPAN>	SUN900</SPAN>
9</SPAN>	6</SPAN>	Station Rd- PX</SPAN>	3</SPAN>	MW137G</SPAN>	3</SPAN>	MW137G</SPAN>
10</SPAN>	7</SPAN>	Robinsons RD- PX</SPAN>	4</SPAN>	MW150G</SPAN>	4</SPAN>	MW150</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL span=6></COLGROUP>

 

[jamtay317]

what column are you trying to sum? what cell is your criteria in?

 

[engineer33]

what column are you trying to sum? what cell is your criteria in?

Sorry table isnt clear every item under PX is a PX and that goes for all so i have 9 equipment of each and each equipment is in a different zone.

I need a formula that displays equipment in zone 1 to zone 9 such as

ZONE 1
PX: 0
Signals:2
Tpws: 2


ZONE 2
PX: 1
Signals:3
Tpws: 3



Thank you

 

[jamtay317]

	H	I	J	K	l	M	N	O	P
1	zone1	zone2	zone3	zone4	zone5	zone6	zone8	zone9	zone10
2	PX: 0	PX: 1	PX: 2	PX: 2	PX: 2	PX: 1	PX: 0	PX: 0	PX: 0
3	Signals: 2	Signals: 3	Signals: 3	Signals: 1	Signals: 0	Signals: 0	Signals: 0	Signals: 0	Signals: 0
4	Tpw: 2	Tpw: 3	Tpw: 3	Tpw: 1	Tpw: 0	Tpw: 0	Tpw: 0	Tpw: 0	Tpw: 0

<tbody>
</tbody>

in h2 copied across

="PX: "&COUNTIFS($A$2:$A$10,COLUMN(A1))

in h3 copied across

="Signals: "&COUNTIFS($C$2:$C$10,COLUMN(A1))

in H4 copied across

="Tpw: "&COUNTIFS($E$2:$E$10,COLUMN(A1))

please let me know how it worked for you