Help with Calculation / Search Strings / Range Data

SamBo1234

Board Regular
Joined
Aug 21, 2006
Messages
77
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.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Probably Pivot Tables/Pivot Charts is the best way to go.....

but if you want formulas, then perhaps something like this?
Book2
ABCDEFGHIJKL
1ERROR NAMEDate OccurredTimes Occured on DateERROR NAME:5/9/20066/9/20067/9/20068/9/2006
2Error One5/9/200610Error One101600
3Error Two6/9/20060Error Two9000
4Error Three7/9/200610Error Three08100
5Error Four8/9/20061Error Four0001
6Error Five5/9/200612Error Five0000
7Error One6/9/200613
8Error Two5/9/20064
9Error Three5/9/20060
10Error Four6/9/20060
11Error Five8/9/20062
12Error One6/9/20063
13Error Two5/9/20065
14Error Three6/9/20068
15Error Four9/9/20064
16Error Five8/9/20065
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

Board Regular
Joined
Aug 21, 2006
Messages
77
I got it :)

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

TY alls!.
 

Forum statistics

Threads
1,141,734
Messages
5,708,166
Members
421,549
Latest member
Dtcfire

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top