BradleyS
Active Member
- Joined
- Oct 28, 2006
- Messages
- 333
- Office Version
- 2010
- Platform
- Windows
Excel 2010
I'm struggling to get the correct count of unique IDs in a date range from a table, that includes additional criteria as shown below.
The formula works without the REASON and SOURCE added to the formula, but I need these to define the correct counts.
These are my 2 formulas using Ctrl+Shift+Enter that aren't working:
=SUMPRODUCT(IF((EVENTDATE<=$G$2)*(EVENTDATE>=$F$2), COUNTIFS(EVENTDATE, "<="&$G$2, EVENTDATE, ">="&$F$2, ID, ID,REASON,"Special Risk",SOURCE, "In House"), 0))
=SUMPRODUCT(IF((EVENTDATE<=$G$2)*(EVENTDATE>=$F$2), COUNTIFS(EVENTDATE, "<="&$G$2, EVENTDATE, ">="&$F$2, ID, ID,REASON,"Routine",SOURCE, "In House"), 0))
It is using DNRs of each column header starting from row 2 to row12.
The date range must be within the period shown in F1 and G1. I have shown the dates in the table in red that should not be counted.
The count should show as 2 for both formulas, but is showing as 5 and 7.
What am I doing wrong?
I'm struggling to get the correct count of unique IDs in a date range from a table, that includes additional criteria as shown below.
The formula works without the REASON and SOURCE added to the formula, but I need these to define the correct counts.
These are my 2 formulas using Ctrl+Shift+Enter that aren't working:
=SUMPRODUCT(IF((EVENTDATE<=$G$2)*(EVENTDATE>=$F$2), COUNTIFS(EVENTDATE, "<="&$G$2, EVENTDATE, ">="&$F$2, ID, ID,REASON,"Special Risk",SOURCE, "In House"), 0))
=SUMPRODUCT(IF((EVENTDATE<=$G$2)*(EVENTDATE>=$F$2), COUNTIFS(EVENTDATE, "<="&$G$2, EVENTDATE, ">="&$F$2, ID, ID,REASON,"Routine",SOURCE, "In House"), 0))
It is using DNRs of each column header starting from row 2 to row12.
The date range must be within the period shown in F1 and G1. I have shown the dates in the table in red that should not be counted.
The count should show as 2 for both formulas, but is showing as 5 and 7.
What am I doing wrong?