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:
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: