Hello,
The formula counts the number of occurrences if the IDs are Unique and falls between certain dates. I also want to include the sales person into the mix. In the below example, the results should be one since Bill has one unique ID that falls between the date range.
I tried adding the reference to the formula but could not get it to work.
Any help is appreciated.
The formula counts the number of occurrences if the IDs are Unique and falls between certain dates. I also want to include the sales person into the mix. In the below example, the results should be one since Bill has one unique ID that falls between the date range.
I tried adding the reference to the formula but could not get it to work.
Any help is appreciated.
| Book1 | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | ID | Sales Assoc | Date | 4/1/17 | 4/30/17 | Bill | |||
| 2 | 1471490339 | Bill | 4/11/17 | ||||||
| 3 | 1471490339 | Bill | 4/11/17 | 2 | |||||
| 4 | 1471490339 | Bill | 4/11/17 | ||||||
| 5 | 1471731669 | Rick | 4/26/17 | ||||||
| 6 | 1471731669 | Rick | 4/26/17 | ||||||
| 7 | 1473630635 | Bill | 10/20/17 | ||||||
| 8 | |||||||||
Sheet3 | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F3 | {=SUMPRODUCT(IF((D2:D8<=F1)*(D2:D8>=E1), 1/COUNTIFS(D2:D8, "<="&F1, D2:D8, ">="&E1, B2:B8, B2:B8),0))} | |
| Press CTRL+SHIFT+ENTER to enter array formulas. | ||
