+ Reply to Thread
Results 1 to 19 of 19

COUNTIFS Issue

  1. #1
    Registered User
    Join Date
    05-26-2014
    Posts
    5

    COUNTIFS Issue

    Hi all,

    Just to note - I am using these numbers as an example. I am working on excel 2003. Please see attached image, hopefully if it uploads correctly.

    So I want to calculate productivity of my employees "AA,BB,CC,DD" by the day. So for example I would like to calculate how many times AA sent an email to customers on 5/26/2014. Some customers have more than 1 account. I don't want to count each customer more than once. I also don't want to count customers with 0 Outstanding Documents.

    So how I am doing this is using countifs as shown below. My only issue is how to not include duplicates from Column A - "Customer ID"

    =COUNTIFS($C:$C,$G3,$D:$D,">0",$E,H$2)

    This formula works for everything I need, I just need to not count customer ID's more than once.

    Can anyone advise me on the easiest way to fix this? Not just a simple filter or pivot table as I am well aware of that, it needs to be incorporated into the formula.

    Any help much appreciated.

    ThanksScreen Shot 2014-05-26 at 8.57.37 PM.png

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: COUNTIFS Issue

    Hi and welcome to the forum

    Just an observation...

    either you are not using 2003 or your countifS() is not working. countifS() only came out in 2007 and is not available in 2003.

    Also,Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you Also, not all members can upload picture files (Company firewalls and stuff)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    05-26-2014
    Posts
    5

    Re: COUNTIFS Issue

    Sample Work.xlsx

    Hi Ford,

    Thanks for your input. I have attached the file. I am now using my Mac at home to provide my demonstration. The work I have is at my office where I thought it was 2003, but I'm probably wrong because I use COUNTIFS all the time. This time it just became too complicated for me to figure out.

    Please ask if you are unsure with any of the information provided. To give some indication H5 is showing 3, which I do not want as you can see in row 4 and 5 - it is counting customer ID number 2, three times instead of twice. The customer ID refers to a customer.

    So to clarify, the parameters I want to count are the number of times my employee has emailed a customer by the day. So if one customer has 1,2,3 accounts that email is only counted once.

    So with my current calculation I want to include some way of not counting duplicate customer ID's.

    Let me know if this is clear.

    I appreciate the help.

    Thanks,

    Mark

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: COUNTIFS Issue

    OK, maybe this?

    =MIN(COUNTIFS($C$2:$C$15,$G3,$E$2:$E$15,H$2),1)

  5. #5
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: COUNTIFS Issue

    HI FDibbins,

    I have used the formula which you have provided , its showing "1" in all the cases, i am just trying to help to you both,
    I understood hegleg88 required to count the data under Four categories but not three categories like
    Condition 1 : Employed Assigned should be same
    Condition 2 : Outstanding Documents should be minimum 1 and above with respect to Customer ID i.e it should one more condition 3 is requested like it should search only data of one customer at a time
    Condition 4 :Date

    I have attached sheet for more clarification

    Punnam
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: COUNTIFS Issue

    Do you really want this count employee wise? Instead, you can go with Customer ID wise. Refer Output sheet attached.
    Attached Files Attached Files
    If my assistance has helped, there is a reputation icon * on the left hand corner below the post - you can show your appreciation to the user who has helped in resolving your requirement.

    If your requirement has been solved please mark your thread as Solved.
    In the menu bar above the very first post, select Thread Tools, then select "Mark this thread as Solved".

    Kindly use [FORMULA] or [CODE] tags when posting your code.

    Regards,
    Sarang

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: COUNTIFS Issue

    With an pivot table.

    See the attached file.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  8. #8
    Registered User
    Join Date
    05-26-2014
    Posts
    5

    Re: COUNTIFS Issue

    Thanks for all the responses. However, maybe I was not that clear with my instructions.

    My employees contact customers throughout the day via email, many customers have more than one account so I want this to be counted only once. The employee will enter in the date they contacted a customer via email after it is sent in column E. This allows me to quantify my employees daily productivity.

    So if my fictional employee Charlie Chaplin "CC" sends emails to the customers he has been assigned - customer ID 2 & 4. I want this to show up as 2 emails, even though customer 2 has 2 accounts which satisfy the other criteria (CC is assigned, date is 5/26/2014, documents outstanding >1).

    Right now I am working with a COUNTIFS formula with 3 criteria - Employee that is assigned, specific date, documents outstanding >0. I want to add a 4th criteria for duplicate customer ID's to not be counted.

    The updated spreadsheet shows the result I am getting with my 3 criteria in red and the one I want in green, which I manually entered in. This is because it is a tiny amount of data. The spreadsheet I am working off is huge and each employee is assigned to over 100 customers, most with more than 1 account. Only 1 employee can be assigned to one customer and all their accounts.

    Again, a reminder - I do not want Pivot tables or filters to determine this, I want to somehow incorporate my 4th criteria into the formula somehow. If anyone has any great suggestions please let me know.

    Thanks,

    MarkSample Work Updated.xlsxSample Work Updated.xlsx

  9. #9
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: COUNTIFS Issue

    try below in H3
    copy paste then hold control and shift together and then hit enter
    =SUM(IF(FREQUENCY(IF(($E$2:$E$500=H$2)*($C$2:$C$500=$G3)*($D$2:$D$500)>0,$A$2:$A$500),IF(($E$2:$E$500=H$2)*($C$2:$C$500=$G3)*($D$2:$D$500)>0,$A$2:$A$500))>0,1,0))
    drag down and drag to right
    Last edited by hemesh; 05-27-2014 at 08:10 AM. Reason: forgot to add one criteria**
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  10. #10
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: COUNTIFS Issue

    Quote Originally Posted by hemesh View Post
    Try below in H3
    copy paste then hold control and shift together and then hit enter
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    drag down and drag to right
    Use the below formula to get the employee names. Place it in G3 and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    You can also use SUMPRODUCT instead of SUM in hemesh's formula (quoted above). This too needs to be used as an array formula (confirmed by pressing Ctrl + Shift + Enter after pasting formula) :
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by Saarang84; 05-27-2014 at 08:35 AM.

  11. #11
    Registered User
    Join Date
    05-26-2014
    Posts
    5

    Re: COUNTIFS Issue

    Thanks Hemesh and Saarang84.

    This works great with the data example I provided but when I tried to apply it to my data it returns NA.

    Perhaps this is because the customer ID I initially gave you is too simplified Eg 1,2,3...etc.

    My actual ID's are a letter followed by numbers.

    If you are able to, please take a look at my updated example. Now I am quantifying the emails sent by date and using a little tweak to your formula (removing the date criteria) to give me the total emails sent from each employee.

    This all works well with my initial example - Once I change the format of the customer ID's it returns 0?

    Any pointers much appreciated.

    Thanks
    Sample Work Updated 2.xlsx

  12. #12
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: COUNTIFS Issue

    do you have any table or How do you assign customer ID ?
    Last edited by hemesh; 05-27-2014 at 12:39 PM.

  13. #13
    Registered User
    Join Date
    05-26-2014
    Posts
    5

    Re: COUNTIFS Issue

    We have a masterlist of customers which have already been manually assigned. No customer has more than 1 employee assigned to them. Even if the customer has 10 accounts, they still only have one employee assigned. I need this to determine the productivity of the employees.

    What threw off your formula I believe was when I incorporated it into my data where customer ID's are letters and numbers rather than a simple 1, 2, 3 etc...

    Do you know what needs to be adjusted?

    Really appreciate it!

    Mark

  14. #14
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: COUNTIFS Issue

    In the second sheet updated first unmerge the cells , in h2 type 26-05-2014 in I2 type 27-05-2014
    then in H3 copy paste below then hold control and shift together and then hit enter ,
    =SUM(IFERROR(1/COUNTIFS($C$2:$C$16,$G3,$D$2:$D$16,">0",$E$2:$E$16,H$2,$A$2:$A$16,INDEX(IF(($C$2:$C$16=$G3)*($D$2:$D$16>0)*($E$2:$E$16=H$2),$A$2:$A$16),0)),0))

    drag down and drag to the right.

    if you remove the date parameter in above formula then similar customer ID whether on 26 or 27 will be counted as One.
    Attached Files Attached Files
    Last edited by hemesh; 05-27-2014 at 03:52 PM.

  15. #15
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: COUNTIFS Issue

    @Hemesh : Your below array formula works brilliant..
    Quote Originally Posted by hemesh View Post
    .. =SUM(IFERROR(1/COUNTIFS($C$2:$C$16,$G3,$D$2:$D$16,">0",$E$2:$E$16,H$2,$A$2:$A$16,INDEX(IF(($C$2:$C$16=$G3)*($D$2:$D$16>0)*($E$2:$E$16=H$2),$A$2:$A$16),0)),0))
    I also observed that using SUMPRODUCT in place of SUM also gives the same results. Can you kindly explain how it works??
    Last edited by Saarang84; 05-27-2014 at 09:21 PM.

  16. #16
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: COUNTIFS Issue

    Quote Originally Posted by Punnam View Post
    HI FDibbins,
    I have used the formula which you have provided , its showing "1" in all the cases, i am just trying to help to you both,

    Then perhaps you had a match for all the countifs() criteria.
    =MIN(COUNTIFS($C$2:$C$15,$G3,$E$2:$E$15,H$2),1)
    If there is no match, MIN () will give 0, if there is any amount of matches, MIN() will give 1

  17. #17
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: COUNTIFS Issue

    Quote Originally Posted by FDibbins View Post
    Then perhaps you had a match for all the countifs() criteria.
    =MIN(COUNTIFS($C$2:$C$15,$G3,$E$2:$E$15,H$2),1)
    If there is no match, MIN () will give 0, if there is any amount of matches, MIN() will give 1
    Hi Ford,

    Your formula doesn't fulfill what exactly the OP wants.. Hemesh has given the exact solution. Can you explain how it works??

  18. #18
    Registered User
    Join Date
    05-26-2014
    Posts
    5

    Re: COUNTIFS Issue

    Quote Originally Posted by hemesh View Post
    In the second sheet updated first unmerge the cells , in h2 type 26-05-2014 in I2 type 27-05-2014
    then in H3 copy paste below then hold control and shift together and then hit enter ,
    =SUM(IFERROR(1/COUNTIFS($C$2:$C$16,$G3,$D$2:$D$16,">0",$E$2:$E$16,H$2,$A$2:$A$16,INDEX(IF(($C$2:$C$16=$G3)*($D$2:$D$16>0)*($E$2:$E$16=H$2),$A$2:$A$16),0)),0))

    drag down and drag to the right.

    if you remove the date parameter in above formula then similar customer ID whether on 26 or 27 will be counted as One.

    Thanks so much this worked exactly as I needed!!

  19. #19
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: COUNTIFS Issue

    Quote Originally Posted by Saarang84 View Post
    =SUM(IFERROR(1/COUNTIFS($C$2:$C$16,$G3,$D$2:$D$16,">0",$E$2:$E$16,H$2,$A$2:$A$16,INDEX(IF(($C$2:$C$16=$G3)*($D$2:$D$16>0)*($E$2:$E$16=H$2),$A$2:$A$16),0)),0))

    I also observed that using SUMPRODUCT in place of SUM also gives the same results. Can you kindly explain how it works??
    Can someone explain how the above formula coded by Hemesh works ??

  20. #20
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: COUNTIFS Issue

    you are welcome and thanks for the feedback hegleg.

    @ saarang above formula divide the counts of entries with same customer ID and uniques ID's by 1 then sum them giving the expected results.

+ 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. COUNTIFS issue
    By Glen- in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-11-2014, 08:29 PM
  2. Countifs over multiple worksheets issue
    By luciedefreitas in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-28-2014, 09:10 AM
  3. Issue with COUNTIFS formula and dates
    By cstockus in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 7
    Last Post: 11-27-2013, 08:05 PM
  4. COUNTIFS - Time Issue
    By kjcdude in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-24-2013, 06:48 PM
  5. [SOLVED] Sumproduct as Countifs issue
    By jake.masters in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-12-2012, 10:49 AM

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