# Help with Calculation / Search Strings / Range Data

#### SamBo1234

##### Board Regular
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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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.

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.

I got it

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

TY alls!.

Replies
3
Views
338
Replies
1
Views
529
Replies
13
Views
248
Replies
3
Views
252
Replies
2
Views
467

1,219,959
Messages
6,151,153
Members
451,011
Latest member
Pigdog89

### 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.

### Which adblocker are you using?

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

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