+ Reply to Thread
Results 1 to 6 of 6

Separating Data and Counting the data on a different spreadsheet

  1. #1
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    82

    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.

    Thanks in advance for your help.
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,773

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

    Please try at
    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)
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    82

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

    Awesome!!! Thank you very much!!!

  4. #4
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    82

    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.
    Attached Files Attached Files

  5. #5
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,773

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

    Please try at
    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")
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    06-09-2015
    Location
    Statesville, NC
    MS-Off Ver
    365
    Posts
    82

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

    Quote Originally Posted by Bo_Ry View Post
    =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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Separating data groups and formatting certain data text in red
    By terryhenderson in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-17-2019, 12:52 AM
  2. [SOLVED] Looking up data and separating it
    By reedy84 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-10-2015, 02:25 PM
  3. VB Macro for counting data & output result to spreadsheet
    By Red fuji in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-19-2012, 07:13 AM
  4. separating and counting text
    By snowee01 in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 07-15-2009, 06:20 PM
  5. UPDATED - Clensing data using a macro and separating already processed data
    By AK262007 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-19-2009, 03:18 PM
  6. separating data
    By spackler in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-10-2007, 03:45 PM
  7. [SOLVED] Counting data in a spreadsheet
    By Jo in forum Excel - New Users/Basics
    Replies: 8
    Last Post: 02-05-2005, 07:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1