# Separating Data and Counting the data on a different spreadsheet

1. ## Separating Data and Counting the data on a different spreadsheet

Hello everyone,

I have a workbook that I am trying to organize by Operator, Valid Complaints BEFORE 6/30/20 and Valid Complaints AFTER 7/1/20. The raw data will be on the Sheet titled COMPLAINTS -20 and I need to filter through that data and have it sorted in the AGENTS spreadsheet according to the various column headings shown on that sheet. I have already added the formula for column D and E on the AGENTS spreadsheet. I'm just not sure what formulas to use in order to achieve the results I have in A2, B2, and C2. I am using Office 365.

2. ## Re: Separating Data and Counting the data on a different spreadsheet

A2
=FILTER(UNIQUE('Complaints -20'!\$D\$2:\$D\$999),UNIQUE('Complaints -20'!\$D\$2:\$D\$999)<>"")

B2
=COUNTIFS('Complaints -20'!\$D\$2:\$D\$999,A2#,'Complaints -20'!C2:\$C\$999,">="&DATE(2020,{1,6},1),'Complaints -20'!C2:\$C\$999,"<"&DATE(2020,{7,13},0))

D2
=MMULT(B2#,{1;0.25})

E2
=ROUNDUP(D2#,\$F\$1)

3. ## Re: Separating Data and Counting the data on a different spreadsheet

Awesome!!! Thank you very much!!!

4. ## Re: Separating Data and Counting the data on a different spreadsheet

I think I may have spoke to soon. I was looking over the results on the AGENTS sheet and it appears that the formula in column B and C are calculating ALL complaints. I only need it to locate the VALID complaints (indicated by a V in column E of the COMPLAINTS-20 sheet). Also, I'm not to sure I follow the date cutoffs either. Tricia Chambers has 0 valid complaints after 7/1/20, but the AGENTS spreadsheet shows her with 1 valid complaint in column C. I have attached another snapshot of what I found.

5. ## Re: Separating Data and Counting the data on a different spreadsheet

A2
=UNIQUE(FILTER('Complaints -20'!\$D\$2:\$D\$997,'Complaints -20'!\$E\$2:\$E\$997="V"))

B2
=COUNTIFS('Complaints -20'!\$D\$2:\$D\$997,A2#,'Complaints -20'!C2:\$C\$997,">="&DATE(2020,{1,7},1),'Complaints -20'!C2:\$C\$997,"<"&DATE(2020,{7,13},1))

or only count VALID

=COUNTIFS('Complaints -20'!\$D\$2:\$D\$997,A2#,'Complaints -20'!C2:\$C\$997,">="&DATE(2020,{1,7},1),'Complaints -20'!C2:\$C\$997,"<"&DATE(2020,{7,13},1),'Complaints -20'!\$E\$2:\$E\$997,"V")

6. ## Re: Separating Data and Counting the data on a different spreadsheet

Originally Posted by Bo_Ry
=COUNTIFS('Complaints -20'!\$D\$2:\$D\$997,A2#,'Complaints -20'!C2:\$C\$997,">="&DATE(2020,{1,7},1),'Complaints -20'!C2:\$C\$997,"<"&DATE(2020,{7,13},1),'Complaints -20'!\$E\$2:\$E\$997,"V")
This one done the trick! Thanks!

There are currently 1 users browsing this thread. (0 members and 1 guests)