Totaling Heat Types by Towns

[mkeohan]

Hello Everyone,

I have a large list of addresses that have heat system types associated with them. I want to be able to get it into a format where it is just the town name and a total number of each heat type. I would normally do this manually but there are over 1 million addresses in this file. This forum wont let me post attachments for whatever reason so I have copied it below.

					Town							HEAT SYS
			CONSTITUTION AVE	ABINGTON	MA	02351		2308		745	ELC
			CONSTITUTION AVE	ABINGTON	MA	02351		2308		844	ELC
			CONSTITUTION AVE	ABINGTON	MA	02351		2307		719	ELC
			Jennings Dr	Abington	MA	02351		1526		267	Gas
			Jennings Dr	Abington	MA	02351		1526		366	Gas
			Jennings Dr	Abington	MA	02351		1525		475	Gas
			CENTRE AVE	ABINGTON	MA	02351		2229		374	Oil
			CENTRE AVE	ABINGTON	MA	02351		2229		338	Oil
			CHERRY ST	ABINGTON	MA	02351		2514		142	Oil
			Pond St		Avon	MA	02322	02322-1624	1624		431	Gas
			Pond St		Avon	MA	02322	02322-1624	1624		297	Gas
			Pond St		Avon	MA	02322	02322-1624	1624		954	Gas
			KING ST		AVON	MA	02322	02322-1205	1205		058	Oil
			BLANCHARD ST	AVON	MA	02322	02322-1244	1244		064	Oil
			BLANCHARD ST	AVON	MA	02322	02322-1244	1244		082	Oil

<colgroup><col style="width:48pt" width="64" span="5"> <col style="width:48pt" width="64"> <col style="width:48pt" width="64" span="6"> <col style="width:48pt" width="64"> </colgroup><tbody>
</tbody>

The desired result would be:

	ELEC	GAS	OIL
Abington	3	3	3
Avon		3	3

<colgroup><col style="width:48pt" width="64" span="4"> </colgroup><tbody>
</tbody>

The two columns in questions are F=Towns and M=Heat Type

Please let me know if any of you can help. Thanks!

 

[Joe4]

You can use COUNTIFS to do this.

Suppose that your data is in rows 2:16.
And then your summary has the town names in column P (so "Abington" is in cell P2, and "Avon" is in cell P3)
and the heat types are in cells Q1:S1.

Then place this formula in cell Q2 and copy to S3:

=COUNTIFS($F$2:$F$16,$P2,$M$2:$M$16,Q$1)

 

[sandy666]

using PowerQuery
- remove unnecessary columns
- capitalize words
- duplicate column
- pivot

Column1

Town

Column2

Column3

Column4

HEAT SYS

Column5

Column6

Town

ELC

Gas

Oil

CONSTITUTION AVE

ABINGTON

MA

2351​

2308​

745​

ELC

Abington

3​

3​

3​

CONSTITUTION AVE

ABINGTON

MA

2351​

2308​

844​

ELC

Avon

0​

3​

3​

CONSTITUTION AVE

ABINGTON

MA

2351​

2307​

719​

ELC

Jennings Dr

Abington

MA

2351​

1526​

267​

Gas

Jennings Dr

Abington

MA

2351​

1526​

366​

Gas

Jennings Dr

Abington

MA

2351​

1525​

475​

Gas

CENTRE AVE

ABINGTON

MA

2351​

2229​

374​

Oil

CENTRE AVE

ABINGTON

MA

2351​

2229​

338​

Oil

CHERRY ST

ABINGTON

MA

2351​

2514​

142​

Oil

Pond St

Avon

MA

2322​

02322-1624

1624​

431​

Gas

Pond St

Avon

MA

2322​

02322-1624

1624​

297​

Gas

Pond St

Avon

MA

2322​

02322-1624

1624​

954​

Gas

KING ST

AVON

MA

2322​

02322-1205

1205​

58​

Oil

BLANCHARD ST

AVON

MA

2322​

02322-1244

1244​

64​

Oil

BLANCHARD ST

AVON

MA

2322​

02322-1244

1244​

82​

Oil

 

[mkeohan]

Still having trouble here. Is what I used with that for that formula =COUNTIFS($F$2:$F$937063,$CE2,$M$2:$M$937063,CF$1) am I doing it right? I only get a zero as an answer. Those are the data ranges I had to move the P and Q down because I didn't include those rows for privacy reasons

 

[Joe4]

Try hard-coding the first one, just to test, i.e.

[COLOR=#333333]=COUNTIFS($F$2:$F$937063,"Abington",$M$2:$M$937063,"ELC")[/COLOR]

If that does not work, that probably means that you have extra characters in your data.
Check a row that should match, and see if there are any extra spaces on the word "ABINGTON" or "ELC".

 

[mkeohan]

That worked! It gave me a value that is correct. How to I apply it to the whole sheet?

 

[mkeohan]

By the way Joe4 excellent choice of avatar!! My favorite comic of all time!

 

[Joe4]

That worked! It gave me a value that is correct. How to I apply it to the whole sheet?

Is "Abington" in CE2 and "ELC" in "CF1"?
If so, then your original formula should have worked too.
If not, then you either have the wrong addresses there, or you have a typo in one of those cells.

 

[mkeohan]

Abington the word didn't show up just the value total of ELC for Abington

 

[mkeohan]

I should also point out that ELC as a label didn't show up either