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

virtuosok

Board Regular
Joined
Sep 2, 2020
Messages
137
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
12,682
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

virtuosok

Board Regular
Joined
Sep 2, 2020
Messages
137
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
12,682
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 2, 2020
Messages
137
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Dec 30, 2008
Messages
12,682
Office Version
  1. 365
Platform
  1. Windows
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.

When I asked about your default date format, I was referring to your system date format, not the cell format in excel.

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
Joined
Sep 2, 2020
Messages
137
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Attachments

  • Capture1.PNG
    Capture1.PNG
    36.3 KB · Views: 2

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,682
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
12,682
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 2, 2020
Messages
137
Office Version
  1. 2016
Platform
  1. Windows
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".
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top