Help with Calculation / Search Strings / Range Data

[SamBo1234]

Hi All,

Another Problem i have, I have the data such as below:

GRAPH DETAILS

------------------------------------------DATE
ERROR NAME:----05/09/2006-06/09/2006-07/09/2006-08/09/2006
Error One
Error Two
Error Three
Error Four
Error Five


DATA (Stored on another Worksheet)


ERROR NAME-----Date Occurred-----Times Occured on Date
Error One-----05/09/2006-----10
Error Two-----06/09/2006-----0
Error Three-----07/09/2006-----10
Error Four-----08/09/2006-----1
Error Five-----05/09/2006-----12
Error One-----06/09/2006------13
Error Two-----05/09/2006------4
Error Three-----05/09/2006------0
Error Four-----06/09/2006------0
Error Five-----08/09/2006-----2
Error One-----06/09/2006-----3
Error Two-----05/09/2006-----5
Error Three-----06/09/2006-----8
Error Four-----09/09/2006-----4
Error Five-----08/09/2006-----5

My problem is that i need to populate my GRAPH details with the number of times each error has occured in total for the same date.

For example in my GRPAH DETAILS the value below 05/09/2006 and to the right of Error One... would display a value of 10 as it has occured ten times in total in my DATA on the 05/09/2006.

And another Example would be that below 05/09/2006 and to the right of Error Two... would display a value of 9 because it occured 4 times in 05/09/2006 and again another 5 times on the 05/09/2006.

Any Formulas to help me peform this... i have a huge amount of data and cant seem to figure out a simple way to perform the calculations.

I know this is a big post, sorry!.

Cheers all.

 

[onlyadrafter]

Hello

assuming your data starts in A1 on both sheets try

=SUMPRODUCT(--(Sheet2!$A$2:$A$16=Sheet1!$A2),--(Sheet2!$B$2:$B$16=Sheet1!B$1)*(Sheet2!$C$2:$C$16))

in B2 for Error One (in A2) and 5/9/06 (in B1) copy down and over.


Alternately you could use PIvot Tables somehow.

 

[NBVC]

Probably Pivot Tables/Pivot Charts is the best way to go.....

but if you want formulas, then perhaps something like this?

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1	ERROR NAME	Date Occurred	Times Occured on Date				ERROR NAME:	5/9/2006	6/9/2006	7/9/2006	8/9/2006
2	Error One	5/9/2006	10				Error One	10	16	0	0
3	Error Two	6/9/2006	0				Error Two	9	0	0	0
4	Error Three	7/9/2006	10				Error Three	0	8	10	0
5	Error Four	8/9/2006	1				Error Four	0	0	0	1
6	Error Five	5/9/2006	12				Error Five	0	0	0	0
7	Error One	6/9/2006	13
8	Error Two	5/9/2006	4
9	Error Three	5/9/2006	0
10	Error Four	6/9/2006	0
11	Error Five	8/9/2006	2
12	Error One	6/9/2006	3
13	Error Two	5/9/2006	5
14	Error Three	6/9/2006	8
15	Error Four	9/9/2006	4
16	Error Five	8/9/2006	5
17
Sheet1

Formula in H2: =SUMPRODUCT(--($A$2:$A$16=$G2),--($B$2:$B$16=H$1),$C$2:$C$16) copied across and down.

Adjust ranges to suit and add sheet names as appropriate for proper referencing.....Note: Watch the placement of the $-signs.

 

[SamBo1234]

I got it

Excellent Job, thanks guys this has saved me ALOT of time.

TY alls!.