# Divide the total only when the criteria matches

#### mahmed1

##### Well-known Member
Hi

I have this code to calculate my conc rate

ROUND((SUM(t.[CUMULATIVE ENGAGED CHAT TIME])/SUM(t.[ENGAGED TIME])),2) AS Concurreny_Rate

What ive just realised is that the ENGAGED TIME could he duplicate times in there so i need to only take the 1 time

IF The Agent and Hour is the same only sum 1 of those ENGAGED Times (There could be the same times and agents but the Skill could be different) - This is throwing out my total engaged times

How can i get around this

Thank YOu

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

#### mahmed1

##### Well-known Member
As an example

if the data was like this

datetime, Agent, skill, Cumalative Engaged Time, Engaged Chat Time

20/07/2020 08:00, Agent1, CD, 02:00, 01:10:00
20/07/2020 08:00, Agent1, CQ, 01:00, 01:10:00

As the Agent and datetime is the same (skill different) the formula should be 03:00/01:10:00 (So only take 1 of the Engaged time)

ROUND((SUM(t.[CUMULATIVE ENGAGED CHAT TIME])/SUM(
IF(Agent Column and DateTime Column = same then take 1 of the Engaged Time),2) AS Concurreny_Rate

I can do this in Excel where i put 0’s in that column before i import but that wouldmean chaging the database

hoping i can do that in Access in that formula

#### xenou

##### MrExcel MVP
Hi the general purpose solution is to write a query first to get the records you are interested in summing or averaging. That would be step one. Then you write a query that uses the first query as the data source and do your actual summing or averaging. That would be step two.

In this case I don't really see how you will proceed precisely. Data looks a little ugly. But anyway, the two part approach as described above is basically the way to go (and it is really part one that is the key part as the next part will be your current query basically).

#### Sahil123

##### Well-known Member
Thank You - i think with this data set - it might be better to clean out in excel and then import in

#### Sahil123

##### Well-known Member
Only probs i have at the moment is that i have a table that has got several months of data that is already stored in access so for now would have been nice to clean existing table in access and then going forward import in clean data

is there a quick way of deleting all the data and then after copying into excel and then copy and paste the data from excel to access (copy and paste)

#### xenou

##### MrExcel MVP
You can write a delete query to delete all the data in a table:
Code:
``delete * from Table1;``

Copy and paste I think works (I don't use that a lot but I'm pretty sure I have done it). Also you can use the Import from Excel feature which is my goto method.

Replies
7
Views
217
Replies
2
Views
109
Replies
1
Views
698
Replies
1
Views
264
Replies
2
Views
1K

1,133,582
Messages
5,659,652
Members
418,518
Latest member
chantel

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