# =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  Register To Reply

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.  Register To Reply

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

Hi,

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

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,"")  Register To Reply

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

Or use a pivotable.
test area (1).xlsx  Register To Reply

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.  Register To Reply

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?  Register To Reply

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

Maybe - attach a new workbook showing the issue.  Register To Reply

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

New workbook where my formula is functioning now attached.  Register To Reply

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

Any ideas on this?  Register To Reply

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.  Register To Reply

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