need formula help in Excel 2010

[Legacy 410705]

I have a spreadsheet set up as:

date utility units used cost

7/2/2016 gas 122 $94.86
7/12/2016 electric 98 $100.00
2/15/2017 water 99 $56.87
2/20/2017 gas 120 $122.25
4/28/2017 electric 147 $134.57

I would like to extract say all "gas" with units and organize them by date in another part of the spreadsheet so I can graph units against year. Ie. all gas sorted by year with total units. Is this possible? Is thee some other way to do this? My old "123" program had a function to do this. Thanks for any help.

 

[alansidman]

Use the filter function on the Data Tab. Filter on the item Gas and then copy and paste to a new location. Clear the filter.

 

[Legacy 410705]

Use the filter function on the Data Tab. Filter on the item Gas and then copy and paste to a new location. Clear the filter.

Thanks for the help. I'll see if I can figure it out.

oldguy2

 

[Oeldere]

I would solve that using Pivot table.

 

[Oeldere]

Book1
	A	B	C	D	E	F	G
1	data	description	-	value	Year	month	day
2	7-2-2016	gas	122	94,86	2016	02	07
3	7-12-2016	electric	98	100	2016	12	07
4	15-2-2017	water	99	56,87	2017	02	15
5	20-2-2017	gas	120	122,25	2017	02	20
6	28-4-2017	electric	147	134,57	2017	04	28
Blad10

Cell Formulas
Range	Formula
E2	=YEAR(A2)
F2	=MONTH(A2)
G2	=DAY(A2)

Below a pivot table.

Book1
	K	L	M	N	O
10
11	Som van value	Kolomlabels
12	Rijlabels	02	04	12	Eindtotaal
13	electric		134,57	100,00	234,57
14	2016			100,00	100,00
15	2017		134,57		134,57
16	gas	217,11			217,11
17	2016	94,86			94,86
18	2017	122,25			122,25
19	water	56,87			56,87
20	2017	56,87			56,87
21	Eindtotaal	273,98	134,57	100,00	508,55
Blad10

 

[Legacy 410705]

#FFFFFF ;border: 1px solid;border-collapse: collapse; border-color: #BBB">

	A	B	C	D	E	F	G
data	description	-	value	Year	month	day
gas
electric
water
gas
electric

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]7-2-2016[/TD]

[TD="align: right"]122[/TD]
[TD="align: right"]94,86[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]02[/TD]
[TD="align: right"]07[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]7-12-2016[/TD]

[TD="align: right"]98[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]07[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]15-2-2017[/TD]

[TD="align: right"]99[/TD]
[TD="align: right"]56,87[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]02[/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]20-2-2017[/TD]

[TD="align: right"]120[/TD]
[TD="align: right"]122,25[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]02[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]28-4-2017[/TD]

[TD="align: right"]147[/TD]
[TD="align: right"]134,57[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]04[/TD]
[TD="align: right"]28[/TD]

</tbody>

Blad10​

#FFFFFF " >[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas#FFFFFF ;border-collapse: collapse; border-color: #BBB">[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=YEAR(A2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=MONTH(A2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=DAY(A2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]




Below a pivot table.

#FFFFFF ;border: 1px solid;border-collapse: collapse; border-color: #BBB">#DAE7F5 " />#DAE7F5 ;text-align: center;color: #161120">

	K	L	M	N	O
Som van value	Kolomlabels
Rijlabels	Eindtotaal
electric
2016
2017
gas
2016
2017
water
2017
Eindtotaal

<colgroup><col style="width: 25pxpx" https:="" www.mrexcel.com="" forum="" usertag.php?do="list&action=hash&hash=DAE7F5"" target="_blank"></colgroup><colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]02[/TD]
[TD="align: right"]04[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"]134,57[/TD]
[TD="align: right"]100,00[/TD]
[TD="align: right"]234,57[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]100,00[/TD]
[TD="align: right"]100,00[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"]134,57[/TD]
[TD="align: right"][/TD]
[TD="align: right"]134,57[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]217,11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]217,11[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]94,86[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]94,86[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]122,25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]122,25[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]56,87[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]56,87[/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]56,87[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]56,87[/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]273,98[/TD]
[TD="align: right"]134,57[/TD]
[TD="align: right"]100,00[/TD]
[TD="align: right"]508,55[/TD]

</tbody>

#DAE7F5 ;color: #161120">Blad10​

Thanks Oeldere for your your help. I'll see if I can work it out. I thought I needed IF statements. I appreciate all the help.

oldguy2

 

[Legacy 410705]

re Oeldere : Thanks again - I got it working now. Danke

oldguy2

 

[Oeldere]

You're welcome. We appreciate the feedback!