SUM(IF(FREQUENCY(IF - looking into 2 columns

[virtuosok]

Hi,
The below formula works well for my purposes:
{=SUM(IF(FREQUENCY(IF('Seizure diary'!$E$13:$E$50000=C3,IF('Seizure diary'!$V$13:$V$50000<>"",'Seizure diary'!$F$13:$F$50000+0)),ROW(INDIRECT("1:60000")))+FREQUENCY(IF('Reminder diary'!$E$13:$E$50000=C3,'Reminder diary'!$F$13:$F$50000+0),ROW(INDIRECT("1:60000"))),1))}
Questions:
1. Can I make it work by looking not just into one column (F) but into two columns, M and O? These 2 columns contain cells either with dates or blanks, see below
2. Info in columns M and O gets pulled from a third party's database and whenever there's a date in these columns, it is not formatted as such. I can work around that by setting up helper columns with DATEVALUE/formatting. However, if there's a way to convince the above formula that entries in M and O should be treated as dates without this interim step, it would even be better.
Also pasting the clip from the actual database with source data:

 

[jasonb75]

There is missing information in the example that is relevant to the formula, are we to assume that column V contains the datevalue helper formula or is this an additional criteria that needs to be retained?

What is in the blank cells in columns M and O? Are they empty or do they contain null stings as is often the case with imported data? This can be tested by a simple formula,

='Seizure diary'!M13+0

where M13 refers to one of the blank cells in the data range. An empty cell will return 0, a null string will return a #VALUE! error.

Also, what is the full intention of the formula? At a glance (without setting up a test sheet to evaluate) it appears to be counting unique dates based on the subject in column E, however there don't appear to be any duplicate dates for any given subject in the screen capture shown which means that it should be possible to get the same result with a simple countifs formula.

 

[virtuosok]

Thanks jasonb75,
- column V contains additional criteria which must be preserved (essentially it either contains a number or is empty)
- blank cells in columns M and O contain null strings (tested the formula above, it returns #VALUE)
- there actually are duplicate dates in column F (yes they are not in the clip above, but they exist within the database). Same applies to "dates" in columns M and O. The intent of the formula is to figure out subject diary completion compliance: sometimes we have entries every day, sometimes certain days get missed, and sometimes there are multiple entries on the same day. There are 2 types of diary, one called "Seizure diary" and the other one, "Reminder diary", their outline is very similar and they are posted on separate tabs, appropriately named.As long as there is at least one entry on a given day, at least in one diary type, the subject is considered compliant that day and as such, this entry/date should only be calculated once. As a 2nd step after this formula does the initial calc, I do a simple percentage assessment: overall subject compliance = # of days with at least one entry in the diary * 100 / number of days in the study
The formula in question is supposed to do the calc in bold above.
Apologies for not being clear from the outset!

​

 

[jasonb75]

I think that gives me enough to go on, I'm going to be offline for a few hours, I'll check later and post a new formula for you if it hasn't already been resolved.

 

[jasonb75]

One thing that just came to mind, is it possible to have a date in both columns (M and O) in the same row?
If it is possible, would both be the same or could they be different?

 

[virtuosok]

Thanks for considering all eventualities! No, one row would only contain entry in column M (or in column O, or in neither)... but not in both of them.

 

[jasonb75]

See if this does what you need, array confirmed the same as your existing formula.

=SUM(IF(FREQUENCY(IF('Seizure diary'!$E$13:$E$50000=C3,IF('Seizure diary'!$V$13:$V$50000<>"",IFERROR('Seizure diary'!$M$13:$M$50000+0,IFERROR('Seizure diary'!$O$13:$O$50000+0,"")))),ROW(INDIRECT("1:60000")))+FREQUENCY(IF('Reminder diary'!$E$13:$E$50000=C3,IFERROR('Reminder diary'!$M$13:$M$50000+0,IFERROR('Reminder diary'!$O$13:$O$50000+0,""))),ROW(INDIRECT("1:60000"))),1))

One thing to note, unless this formula is only going into one single cell I would go with the helper columns for efficiency. Given the size of the arrays in the formula you could be looking at slow calculation times.

I assume that you're using INDIRECT to allow for rows being inserted / deleted, otherwise it would be better to use an absolute range without INDIRECT in order to remove volatility from the formula. Also consider using a smaller range for that part of the formula, ROW($A$43101:$A$46388) is adequate for a 10 year period starting 1/1/2018 with an 80% reduction in the array size and calculation effort required for that part of the formula. If it is only needed for the current year then the range can be further reduced for a bigger improvement.

Similarly, if the actual data range in the 2 sheets is significantly less than 50000 rows then consider reducing the range in that part of the formula as well.

 

[virtuosok]

Thank you, thank you, thank you!!! This seems to work perfectly.
Special thanks for trying to dissect this quite complex case even though I provided too little info initially.
+thanks for the INDIRECT component suggestion. I actually reduced the range quite a bit, from 1:60000 to 44044:45100 as this is enough for my purposes.