+ Reply to Thread
Results 1 to 11 of 11

=Count(IF(Frequency(IF This formula is giving me issues...

  1. #1
    Registered User
    Join Date
    12-01-2017
    Location
    Manc, England
    MS-Off Ver
    2007
    Posts
    20

    =Count(IF(Frequency(IF This formula is giving me issues...

    Hi,

    I discovered in a report at work that a formula was not working, after much tinkering with it and a lot of trial-by-error myself I thought I had fixed it yesterday via

    {=COUNT(IF(FREQUENCY(IF((BU="Team 1"),IF((Month=B1),IF((RTW_Evi=""),IF((PayType="PAYE"),CanID)))),CanID),1))}

    This did pull through results, so the fact that today it doesn't leads me to believe I rushed off too soon and might have witnessed some odd kind of artefacting on the screen whilst the formulas got to 'working' and the sheet was processing.

    The intention of the formula is to look at the named fields and match the criteria therein, and bring me back a count of those contractors who are in Team 1, the month matches, the Pay type is PAYE, they are missing any RTW Evidence, and to stop double-counting of the same individual check it against the CanID number.

    Can you help me? Example file attached.

    I know I am likely being dense and have misordered the parenthesis or a comma at some point
    Attached Files Attached Files
    Last edited by Chambew; 08-17-2018 at 10:23 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: =Count(IF(Frequency(IF This formula is giving me issues...

    Please repost the workbook with the expected results entered manually into the summary table.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    12-01-2017
    Location
    Manc, England
    MS-Off Ver
    2007
    Posts
    20

    Re: =Count(IF(Frequency(IF This formula is giving me issues...

    Hi,

    I have attached it to the original post (1).

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: =Count(IF(Frequency(IF This formula is giving me issues...

    I don't see any issue with duplication. This produces the results you want on your sample:

    =COUNTIFS('Base DATa'!$A$2:$A$6090,"Team 1",'Base DATa'!$E$2:$E$6090,B1,'Base DATa'!$C$2:$C$6090,"PAYE",'Base DATa'!$D$2:$D$6090,"")

  5. #5
    Valued Forum Contributor
    Join Date
    04-01-2015
    Location
    The Netherlands
    MS-Off Ver
    2003/2007/2010/2016/office 365
    Posts
    880

    Re: =Count(IF(Frequency(IF This formula is giving me issues...

    Or use a pivotable.
    test area (1).xlsx

  6. #6
    Registered User
    Join Date
    12-01-2017
    Location
    Manc, England
    MS-Off Ver
    2007
    Posts
    20

    Re: =Count(IF(Frequency(IF This formula is giving me issues...

    @AliGW - There isn't an issue with duplication as it stands presently, but if I replicate a line in January then the formula counts it twice. The formula needs to factor more so column B and who has information missing not necessarily how many times they have it missing. so going forward I need to factor this in.

    @Vraag en antwoord - That does work to count it, but as above, same issue. Also, the sample data I have is redacted and the results are part of a larger table.

    The original formula which didn't work either is as follows:

    {=COUNT(IF(FREQUENCY(IF((BU="Team 1")*(PayType="PAYE")*(Month=B1)*(RTW_Evi=""),CanID),CanID),1))}

    I don't know if this original formula worked for the person who created it but it doesn't for me. As I read it though, it tries as I did to utilise the Candidate ID as a measure to prevent double counting.

  7. #7
    Registered User
    Join Date
    12-01-2017
    Location
    Manc, England
    MS-Off Ver
    2007
    Posts
    20

    Re: =Count(IF(Frequency(IF This formula is giving me issues...

    In a further bizarre twist I have further tinkered with my own formula
    {=COUNT(IF(FREQUENCY(IF((BU="Team 1"),IF((Month=B1),IF((RTWEvidence=""),IF((PayType="PAYE"),CandidateID)))),CandidateID),1))}
    and this has pulled through the results I expected to get. When I have transferred it to the master file where this originates and amended the named fields to reflect the correct ones in the main location, it isn't working and I cannot fathom why...

    In actual location
    {=COUNT(IF(FREQUENCY(IF((BU="Team 1"),IF((Month=B1),IF((RTW_Evi=""),IF((PayType="PAYE"),CanID)))),CanID),1))}

    Am I missing something?

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: =Count(IF(Frequency(IF This formula is giving me issues...

    Maybe - attach a new workbook showing the issue.

  9. #9
    Registered User
    Join Date
    12-01-2017
    Location
    Manc, England
    MS-Off Ver
    2007
    Posts
    20

    Re: =Count(IF(Frequency(IF This formula is giving me issues...

    New workbook where my formula is functioning now attached.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    12-01-2017
    Location
    Manc, England
    MS-Off Ver
    2007
    Posts
    20

    Re: =Count(IF(Frequency(IF This formula is giving me issues...

    Any ideas on this?

  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: =Count(IF(Frequency(IF This formula is giving me issues...

    I changed the named range from RTWEvidence to RTW_Evi and the formulas automatically changed and yielded the same values as before. Of course there is no Team 1 in the sample data that we have been given, so I assume that to either be a place holder for Managed Solutions, Contractor_Manage and Staffing_Solutions_IT or a BU in your main file that was not included in the uploaded sample. My only thought is that there is an extra space that was inadvertently added in the naming of the range and then removed from the formulas. Other than that I can only restate what Ali stated, we need to see a sample that demonstrates the formula not working. Along with that sample we need to know at least a few expected values (manually included).
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Formula to Count Frequency of Reoccurring Number Combinations
    By 2blues13 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-23-2017, 04:46 AM
  2. [SOLVED] Sumproduct formula not giving correct count
    By mmccra2858 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-03-2017, 01:59 PM
  3. [SOLVED] count frequency - formula amendment required
    By Nubian in forum Excel General
    Replies: 10
    Last Post: 12-18-2015, 12:22 PM
  4. [SOLVED] SUM FREQUENCY formula to count distinct values w/ OR condition
    By HeyInKy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-05-2015, 03:55 PM
  5. [SOLVED] If Frequency Formula count based on date is off by 1
    By antexity in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-07-2015, 01:30 PM
  6. count unique (frequency formula)
    By jw01 in forum Excel General
    Replies: 1
    Last Post: 01-12-2015, 10:00 PM
  7. .Find giving me issues
    By aworfolk in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-13-2014, 10:22 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