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
Related:

1 response

Answer 1 / 1
RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016 26
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

Similar discussions

Excel - IF function with multiple words
Nick -
user -

14 responses
Compare values in two columns and return the value from third
ramdubai -
keshav -

11 responses