
A 
B 
C 
D 
E 
F 
G 
H 
I 
J 
1 
Functions 
Suppliers 
Sites 
Due Date 

Operations 




2 
Operations 
Apple 
#N/A 
#N/A 


2019+ 
2018 
2017 
Not known 
3 
Operations 
Orange 
#N/A 
4/13/2020 

Unique Suppliers 
2 
1 
0 
2 
4 
End User Services 
Watermelon 
#N/A 
#N/A 






5 
Operations 
Pineapple 
#N/A 
#N/A 


Orange 
Kiwi 

Apple 
6 
Operations 
Kiwi 
Mulgrave 
4/13/2018 


Pear 


Pineapple 
7 
Research 
Fig 
Mulgrave 
4/13/2018 






8 
Research 
Kiwi 
Mulgrave 
4/13/2018 






9 
Research 
Kiwi 
California 
4/13/2018 






10 
Research 
Kiwi 
California 
4/13/2018 






11 
Research 
Banana 
#N/A 
3/30/2012 






12 
Operations 
Pear 
NA 
9/15/2019 






13 
Software Services 
Berry 
Sydney 
#N/A 






14 
Software Services 
Berry 
Sydney 
7/9/2018 






15 
Software Services 
Grape 
Sydney 
7/9/2018 






16 
Software Services 
Berry 
Arizona 
#N/A 






17 
Software Services 
Berry 
Arizona 
12/12/2017 






Sheet1
In G2 control+shift+enter, not just enter:
=SUM(IF(FREQUENCY(IF(1($B$2:$B$17=""),IF(ISNUMBER($D$2:$D$17),IF($A$2:$A$17=$F$1,IF(YEAR($D$2:$D$17)>=2019,MATCH($B$2:$B$17,$B$2:$B$17,0))))),ROW($B$2:$B$17)ROW($B$2)+1),1))
In G5 control+shift+enter, not just enter, and copy down:
=IFERROR(INDEX($B$2:$B$17,SMALL(IF(FREQUENCY(IF(1($B$2:$B$17=""),IF(ISNUMBER($D$2:$D$17),IF($A$2:$A$17=$F$1,IF(YEAR($D$2:$D$17)>=2019,MATCH($B$2:$B$17,$B$2:$B$17,0))))),ROW($B$2:$B$17)ROW($B$2)+1),ROW($B$2:$B$17)ROW($B$2)+1),ROWS($1:1))),"")
In H3 control+shift+enter, not just enter:
=SUM(IF(FREQUENCY(IF(1($B$2:$B$17=""),IF(ISNUMBER($D$2:$D$17),IF($A$2:$A$17=$F$1,IF(YEAR($D$2:$D$17)=2018,MATCH($B$2:$B$17,$B$2:$B$17,0))))),ROW($B$2:$B$17)ROW($B$2)+1),1))
In H5 control+shift+enter, not just enter, and copy down:
=IFERROR(INDEX($B$2:$B$17,SMALL(IF(FREQUENCY(IF(1($B$2:$B$17=""),IF(ISNUMBER($D$2:$D$17),IF($A$2:$A$17=$F$1,IF(YEAR($D$2:$D$17)=2018,MATCH($B$2:$B$17,$B$2:$B$17,0))))),ROW($B$2:$B$17)ROW($B$2)+1),ROW($B$2:$B$17)ROW($B$2)+1),ROWS($1:1))),"")
In I3 control+shift+enter, not just enter:
=SUM(IF(FREQUENCY(IF(1($B$2:$B$17=""),IF(ISNUMBER($D$2:$D$17),IF($A$2:$A$17=$F$1,IF(YEAR($D$2:$D$17)<=2017,MATCH($B$2:$B$17,$B$2:$B$17,0))))),ROW($B$2:$B$17)ROW($B$2)+1),1))
In I5 control+shift+enter, not just enter, and copy down:
=IFERROR(INDEX($B$2:$B$17,SMALL(IF(FREQUENCY(IF(1($B$2:$B$17=""),IF(ISNUMBER($D$2:$D$17),IF($A$2:$A$17=$F$1,IF(YEAR($D$2:$D$17)<=2017,MATCH($B$2:$B$17,$B$2:$B$17,0))))),ROW($B$2:$B$17)ROW($B$2)+1),ROW($B$2:$B$17)ROW($B$2)+1),ROWS($1:1))),"")
In J3 control+shift+enter, not just enter:
=SUM(IF(FREQUENCY(IF(1($B$2:$B$17=""),IF(ISNA($D$2:$D$17),IF($A$2:$A$17=$F$1,MATCH($B$2:$B$17,$B$2:$B$17,0)))),ROW($B$2:$B$17)ROW($B$2)+1),1))
In I5 control+shift+enter, not just enter, and copy down:
=IFERROR(INDEX($B$2:$B$17,SMALL(IF(FREQUENCY(IF(1($B$2:$B$17=""),IF(ISNA($D$2:$D$17),IF($A$2:$A$17=$F$1,MATCH($B$2:$B$17,$B$2:$B$17,0)))),ROW($B$2:$B$17)ROW($B$2)+1),ROW($B$2:$B$17)ROW($B$2)+1),ROWS($1:1))),"")
The same set up also holds for other items like 'reserach'.
Like this thread? Share it with others