# Help with proofreading the formula SUM(IF(FREQUENCY(IF...

#### virtuosok

##### Board Regular
Hi,
I am struggling with the below array formula as it returns a "too few arguments" error.
Excel Formula:
``=SUM(IF(FREQUENCY(IF('Medication log'!\$E\$13:\$E\$50000=C2,IF('Medication log'!\$H\$13:\$H\$50000,"Yes",IFERROR('Medication log'!\$F\$13:\$F\$50000+0, "")))),ROW(INDIRECT("44044:45100")))+FREQUENCY(IF('Reminder diary'!\$E\$13:\$E\$50000=C2,IF(‘Reminder diary’!\$S\$13:\$S\$50000,”Yes”,IFERROR('Reminder diary'!\$F\$13:\$F\$50000+0,""))),ROW(INDIRECT("44044:45100"))),1))``
Legend:
- analyzing data on Medication log - and on Reminder diary tabs, for cases when entries in column E matches entry in cell C2 on "main" tab
- the goal is to have total number of unique dates across both tabs (there may be partial overlap) for the condition in previous bullet; dates are listed in column F on both tabs
- at the same time, these unique dates must only be counted when there's also a "Yes" in column H in Medication log tab, or "Yes" in column S in Reminder diary tab. Otherwise these dates should be ignored in overall calc.
What am I doing wrong please?

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### jasonb75

##### Well-known Member
I haven't evaluated your formula to see why it isn't working, but here's a different formula which I use for something that I've adapted to your requirements.
Excel Formula:
``=45100-44044+1-NETWORKDAYS.INTL(44044,45100,"0000000",CHOOSE({1,2},IF(('Reminder Diary'!\$E\$2:\$E\$50000=C2)*('Reminder Diary'!\$S\$2:\$S\$50000="Yes"),IFERROR('Reminder Diary'!\$F\$2:\$F\$50000+0,0),0),IF(('Medication Log'!\$E\$2:\$E\$50000=C2)*('Medication Log'!\$S\$2:\$S\$50000="Yes"),IFERROR('Medication Log'!\$F\$2:\$F\$50000+0,0),0)))``
The original purpose of the formula was to reverse the workdays function and count the number of days in a holiday list that fall within a specific period. In the adapted formula, the 'holidays' are the dates that meet the criteria, which are extracted from your 2 sheets by the IF arrays nested within the CHOOSE function.

#### virtuosok

##### Board Regular
Thanks jasonb75,
I am getting a zero with the formula, so the issue is likely with formatting of source tabs; I can't change the formatting easily because this is the data that gets transferred frequently from third party.
So,
- dates on both tabs in column F are in "custom", dd-mmm-yyyy format.
- regarding condition ("Yes") in columns H/S: the entries in these columns are either "Yes" or a null string
Could this result in formula not working?

#### jasonb75

##### Well-known Member
Did you array confirm the formula? Sorry, I should have mentioned that in my reply.

What is your default date format (if you are in a region that uses mm/dd/yyyy by default they you are going to have a lot of problems with this).

#### virtuosok

##### Board Regular

When I Ctrl+Shift+Enter the formula, it now returns #VALUE! error. So yes, as a next step I have just looked into the default date format and changed it to dd-mmm-yyyy. Restarted Excel. Now the array formula still returns zeros. At the same time, I see dozens of matching entries when I do simple =COUNTIFS('Medication log'!\$E\$13:\$E\$50000, C2, 'Medication log'!\$H\$13:\$H\$50000, "Yes")
The obvious problem with COUNTIFS is that if I do it for both tabs ("Medication" and "Reminder"), it won't take into account overlapping dates, hence me trying to find an alternative solution.

#### jasonb75

##### Well-known Member
The formula should not return a #VALUE! error unless you have either changed the formula in some way, or you have existing #VALUE! errors in the data.

If windows is set up for MM/DD/YY dates then any date where the day is 13 or greater will be an error, out of the remaining days the month and year will change places so Jan 2 becomes Feb 1, etc. If this is the case with your data then changing the format in excel will not fix it, a more complex solution will be necessary.

#### virtuosok

##### Board Regular

Yes, I updated the default date format in Windows settings - still returns zeros.

#### Attachments

• Capture1.PNG
36.3 KB · Views: 2

#### jasonb75

##### Well-known Member
Try using =S2+0 or similar on some of the date cells in the 2 sheets, do you get dates, numbers or errors?

Depending on the third party source, there may be problems with the dates that excel is not able to convert regardless of format.

#### jasonb75

##### Well-known Member
Following up on my post above, could you upload your workbook to dropbox or similar and post a link to the file here?

I'm assuming that most of the data will be confidential so I would advise deleting that first, I just need the date columns from both sheets in the raw format that comes from the third party application so that I can see why the formula is failing.

I did have a quick look at the frequency formula to see if I could make that work, but it will fall foul of the same problems.

#### virtuosok

##### Board Regular
Thanks! I have deleted sensitive data and posted the whole workbook in Dropbox
The formula I'm trying to set up is in column AA on tab "Post-processed data".

Replies
7
Views
99
Replies
3
Views
39
Replies
3
Views
104
Replies
2
Views
98
Replies
15
Views
269

1,130,212
Messages
5,640,897
Members
417,177
Latest member
njosh

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back