There is no practical limit to the number of conditions, however when multiplying the conditions like that it acts like AND. Of course a value in C2:C6 cannot be "Reactive" AND "Active" AND "Yes" at the same time.
Here is one way to do it.
Date Yes (2)
| A | B | C | D | E | F | G | H |
1 | Date | Ref | Status | | | | | List |
2 | 15/01/2019 | #111 | Yes | | | | | #111 |
3 | 14/02/2018 | #222 | No | | | | | #333 |
4 | 27/03/2019 | #333 | Active | | | | | #444 |
5 | 30/01/2019 | #444 | Reactive | | | | | |
6 | 12/01/2019 | #555 | No | | | | | |
7 | | | | | | | | |
<tbody>
</tbody>
Spreadsheet Formulas |
Cell | Formula | H2 | =IFERROR(INDEX(B$2:B$6,AGGREGATE(15,6,(ROW(B$2:B$6)-ROW(B$2)+1)/((MONTH(A$2:A$6)>=1)*(MONTH(A$2:A$6)<=3)*(ISNUMBER(MATCH(C$2:C$6,{"Reactive","Active","Yes"},0)))*(YEAR(A$2:A$6)=2019)),ROWS(H$2:H2))),"") |
<tbody>
</tbody> |
<tbody>
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4
Hi Peter,
Currently revisiting this query
I now want to create this into one big list of with different date periods after each other in the list. So i've started with a nested if statement where i'm using your formula (as quoted above) based on how many records i've found in a date range. For example...
IDs | | | | Q1 (Jan-Mar) | Q2 (Apr-Jun) | Q3 (Jul-Sep) | Q4 (Oct-Dec) |
#111 | Jan | | | 3 | 2 | 1 | 0 |
#333 | Mar | | | | | | |
#444 | Jan | | | | | | |
#777 | Apr | | | | | | |
#999 | Jun | | | | | | |
| | | | | | | |
| | | | | | | |
<tbody>
</tbody>
Okay under the quarter headings i'm counting the amount of times it finds a record in that time period and then to get the list i'm using a nested IF function with the formula previously provided
=IF($E$4+2=ROW(A1),IF($E$4+$F$4+2=ROW(A1),[...continued...],Aggregate_Data_Formula(01/04/19 - 30/06/19)),Aggregate_Data_Formula(01/01/19 - 31/03/19))
Dragging this down works until it gets to the changing point - so in my example above it returns the first 3 values that are part of Q1 but then won't return further values. The issue is due to the ROW formula in previously provided formula:
=IFERROR(INDEX
(B$2:B$6,AGGREGATE(15,6,(ROW(B$2:B$6)-ROW(B$2)+1)/((MONTH(A$2:A$6)>=1)*(MONTH(A$2:A$6)<=3)*(ISNUMBER(MATCH(C$2:C$6,{"Reactive","Active","Yes"},0)))*(YEAR(A$2:A$6)=2019)),ROWS(H$2:H2))),"")
If i'm nesting the formulas in IF statements as I propose then the ROW part at the end of formula above needs to also include the reference to the Q1 count; as in it would need to start at
ROWS(A$5:A5) in order to provide the right IDs.
I know that's long winded, apologies if you don't understand.
Thanks,
Matt