Help creating a formula

[Newbie73]

Hello everyone, I would like to automate something with a formula if possible. I've attached a dummy spreadsheet as an example:

Example2.xlsx

In Column B, we have different values but most of them will repeat themselves several times (this is okay and I want to count them all). Column K will only be either "R", "IR" or "Clear" and then Column L a range of dates that can be different days, months or years.

My goal is to automate a kind of table like the one I've done manually for this small example. The original spreadsheet has thousands of values, and I will like to know the total of values that occur between 01/04 (first of April, regardless of the year) and 31/08 (regardless of the year), and then from this total how many values have the value "R", how many have the value "IR" and how many have the value "Clear" from column B.

The same as above for a second interval in time which is the rest of the year, from 01/09 (first of September) and 30/03.

Thank you in advance and please do let me know if I haven't explained myself correctly or if you need any more information to be able to help me.

 

[hagia_sofia]

Check your file if it is what you are looking for (formulas are within the worksheet)...

 

[Newbie73]

Check your file if it is what you are looking for (formulas are within the worksheet)...

Thank you! It seems to work, however I get the error that one or more circular references refers to it's own cell. I know I can ignore this but on the main spreadsheet every time it updates it can give me the error (which could happen many times).

Any ideas why or if we can do something to avoid it?

Edit: Sorted, it was actually one of the percentage formulas ahah. Thank you so much!

 

[Newbie73]

Check your file if it is what you are looking for (formulas are within the worksheet)...

One last thing sorry about it, but any chance of making it work ignoring cells different than a date? As in the Column bit of the formula to be for example L:L or L2:L40000 without giving a value error due to blank cells?

 

[hagia_sofia]

One last thing sorry about it, but any chance of making it work ignoring cells different than a date? As in the Column bit of the formula to be for example L:L or L2:L40000 without giving a value error due to blank cells?

Check the file again; it should address both blanks and other values (i.e. not to count them) - the formulas are simplified.

 

[Sektor]

Result workbook (red text). FYI, you miscalculated values in Q2 (should be 8) and Q6 (should be 19). Also you had circular reference (I fixed it).
For "01/04 to 31/08" case:

=LET(
dates;$L$2:$L$30;
marks;$K$2:$K$30;
days;MAP(dates;LAMBDA(d;DAY(d)));
months;MAP(dates;LAMBDA(d;MONTH(d)));
IFERROR(REDUCE(0;FILTER(marks;(marks=O14)*(days>=1)*(months>=4)*(days<=31)*(months<=8));LAMBDA(prev;curr;prev+1));0))

For "01/09 to 30/03" case (couldn't use VSTACK since if any of arrays is empty, the whole result will be error, that's why calculation is divided):

=LET(
dates;$L$2:$L$30;
marks;$K$2:$K$30;
days;MAP(dates;LAMBDA(d;DAY(d)));
months;MAP(dates;LAMBDA(d;MONTH(d)));
IFERROR(REDUCE(0;FILTER(marks;(marks=O14)*(days>=1)*(months>=9)*(days<=31)*(months<=12));LAMBDA(prev;curr;prev+1));0)
+
IFERROR(REDUCE(0;FILTER(marks;(marks=O14)*(days>=1)*(months>=1)*(days<=30)*(months<=3));LAMBDA(prev;curr;prev+1));0))

 

[Newbie73]

Thank you both! Working perfectly now

 

[Sektor]

You're welcome!

 

[cwstudio2020]

Implementing this feature with Excel formulas is quite complex. I achieved this functionality using SQL. I've created an interface that allows me to import Excel files, and then query the results by directly selecting the start and end months on the interface.

 

[Newbie73]

Implementing this feature with Excel formulas is quite complex. I achieved this functionality using SQL. I've created an interface that allows me to import Excel files, and then query the results by directly selecting the start and end months on the interface. If you need this program, you can send me a message. Feel free to message me if you have any questions.

Thank you for your reply, I'm well over my head with excel as it is, learning how to use SQL would be ideal also because it's more efficient when running in big data sheets, just don't think I have the time or brain to embark on that adventure for now