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

1. ## =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

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

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

Hi,

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

4. ## 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. ## Re: =Count(IF(Frequency(IF This formula is giving me issues...

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

6. ## 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. ## 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. ## Re: =Count(IF(Frequency(IF This formula is giving me issues...

Maybe - attach a new workbook showing the issue.

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

New workbook where my formula is functioning now attached.

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

Any ideas on this?

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

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