Previous Topic Next Topic

Match two unique criteria, total from a third .add to new sheet

Closed
Kazok1
Posts
4
Registration date
Wednesday September 23, 2015
Status
Member
Last seen
September 24, 2015
 - Sep 23, 2015 at 09:21 AM
Kazok1
Posts
4
Registration date
Wednesday September 23, 2015
Status
Member
Last seen
September 24, 2015
 - Sep 24, 2015 at 06:43 AM
Hello
Apologies if this has been answered in another thread, I have tried to ask the question in many different ways but cannot come up with anything that helps.

I have several columns of data regarding damages that contain Date;Item;Volume;Reasons Code in one sheet.

I am trying to define trends.

I am trying to check that if a particular item, in a particular month shows in a table, then the total volume (for that item in that month)is then added together and put into a a column in another sheet.
Eg

Sheet one looks like this (i HAVE ONLY INCLUDED 3 ITEMS - IN REALITY IT WILL BE APAPROX 10)

sheet 1

MONTH....... ITEM.. ........VOLUME... ....REASON
AUG... white tin........... 13.... MISC (PLEASE ADVISE)
AUG... red tin 9 WATER INGRESS
AUG... white tin 60 WATER INGRESS
AUG blue tin 5 MISC (PLEASE ADVISE)
SEPT blue tin 83 MISC (PLEASE ADVISE)
SEPT blue tin 63 WATER INGRESS
SEPT red tin 132 MISC (PLEASE ADVISE)
OCT white tin 51 MISC (PLEASE ADVISE)
OCT red tin 45 WATER INGRESS
OCT white tin 8 WATER INGRESS

Sheet 2 (if I could manage the formula!) would look like this

sheet 2

Month ...white red blue
aug...... 73 .....9....5
sep....... 0.....132....146
oct.......59..... 45....0


I can then chart the trends across several month(if there are any)
I can chart trends no problem, it's the actual formula to pick up the info that I am struggling with

1 reply

Answer 1 / 1
RayH
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
 25
Sep 23, 2015 at 04:57 PM
Have you looked into the use of Pivot Tables and Pivot Charts?
There is no need for the use of formulas.

Here is the data you provided and the resulting Pivot table and Chart



1
Kazok1
Posts
4
Registration date
Wednesday September 23, 2015
Status
Member
Last seen
September 24, 2015
Sep 24, 2015 at 05:41 AM
Thanks RayH - I tend to over complicate things. Thanks I will look at the pivot route.
0
Kazok1
Posts
4
Registration date
Wednesday September 23, 2015
Status
Member
Last seen
September 24, 2015
Sep 24, 2015 at 06:43 AM
hOW DID YOU GET THE MONTH VOLUME TO ACCUMULATE (ie I have several entries for Aug but it your chart it is aded together - when I do it I get individual totals for each entry for aug - making the chart look busy - your chart is exactlyhow i want it to look)
0