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

virtuosok

Board Regular
Joined
Sep 2, 2020
Messages
209
Office Version
  1. 365
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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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.
 
Upvote 0
Solution
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?
 
Upvote 0
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).
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Yes, I updated the default date format in Windows settings - still returns zeros.
 

Attachments

  • Capture1.PNG
    Capture1.PNG
    36.3 KB · Views: 4
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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".
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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
Back
Top