X,#N/A - it is considered as two sites. Even x, y is 2 sites
G3: Control+shift+enter (CSE)...
=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&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),$B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),0))))),ROW($B$2:$B$17)-ROW($B$2)+1),1))
G5: CSE 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&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),$B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),0))))),ROW($B$2:$B$17)-ROW($B$2)+1),ROW($B$2:$B$17)-ROW($B$2)+1),ROWS($1:1))),"")
H3: CSE...
=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&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),$B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),0))))),ROW($B$2:$B$17)-ROW($B$2)+1),1))
H5: CSE 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&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),$B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),0))))),ROW($B$2:$B$17)-ROW($B$2)+1),ROW($B$2:$B$17)-ROW($B$2)+1),ROWS($1:1))),"")
I3: CSE...
=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&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),$B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),0))))),ROW($B$2:$B$17)-ROW($B$2)+1),1))
I5: CSE 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&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),$B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),0))))),ROW($B$2:$B$17)-ROW($B$2)+1),ROW($B$2:$B$17)-ROW($B$2)+1),ROWS($1:1))),"")
J3: CSE...
=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&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),$B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),0)))),ROW($B$2:$B$17)-ROW($B$2)+1),1))
J5: CSE 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&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),$B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),0)))),ROW($B$2:$B$17)-ROW($B$2)+1),ROW($B$2:$B$17)-ROW($B$2)+1),ROWS($1:1))),"")
Assuming too much and qualifying too much are two faces of the same problem.
Sir, your formula is working perfectly alright but i have small query, currently formula is defined for fixed cells like B2:B17. But rows will increasing is it possible to derive the formula as B:B
@Aladin Akyurek
Sir, i have two issues now
1)
currently formula is defined for fixed cells like B2:B17. But rows will increasing is it possible to derive the formula as B:B
2) If operations,apple,#N/A,1/7/2018
operations,apple,london,1/12/2018 - it considered as two
But if data is like
operations,apple,#N/A,1/7/2018
operations,apple,Sweden,1/12/2018
operations,apple,london,1/12/2018 - it needs to be considered as 3 but still considered as two
01. What follows assumes that the data is located in Sheet1. Adjust the sheet name if necessary.
02. Define Lrow in the Name Manager as referring to...
=MATCH(REPT("z",255),Sheet1!$A:$A)
03. Define Functions as...
=Sheet1!$A$2:INDEX(Sheet1!$A:$A,Lrow)
04. Define Suppliers as...
=Sheet1!$B$2:INDEX(Sheet1!$B:$B,Lrow)
05. Define Sites as...
=Sheet1!$C$2:INDEX(Sheet1!$C:$C,Lrow)
06. Define DueDates as...
=Sheet1!$D$2:INDEX(Sheet1!$D:$D,Lrow)
07. Define Ivec as...
=ROW(Functions)-ROW(INDEX(Functions,1,1))+1
The foregoing enables to install a dynamic set up, i.e. a set up which adjusts itself to record addition or deletion.
The formulas can now be rewritten in terms of the dynamic named ranges specified in 1 to 7.
08. G3, control+shift+enter…
=SUM(IF(FREQUENCY(IF(1-(Suppliers=""),IF(ISNUMBER(DueDates),IF(Functions=$F$1,IF(YEAR(DueDates)>=2019,MATCH(Suppliers&"|"&IF(ISNA(Sites),"#",Sites),Suppliers&"|"&IF(ISNA(Sites),"#",Sites),0))))),Ivec),1))
09. G5, control+shift+enter and copy down...
=IFERROR(INDEX(Suppliers,SMALL(IF(FREQUENCY(IF(1-(Suppliers=""),IF(ISNUMBER(DueDates),IF(Functions=$F$1,IF(YEAR(DueDates)>=2019,MATCH(Suppliers&"|"&IF(ISNA(Sites),"#",Sites),Suppliers&"|"&IF(ISNA(Sites),"#",Sites),0))))),Ivec),Ivec),ROWS($1:1))),"")
10. H3, control+shift+enter…
=SUM(IF(FREQUENCY(IF(1-(Suppliers=""),IF(ISNUMBER(DueDates),IF(Functions=$F$1,IF(YEAR(DueDates)=2018,MATCH(Suppliers&"|"&IF(ISNA(Sites),"#",Sites),Suppliers&"|"&IF(ISNA(Sites),"#",Sites),0))))),Ivec),1))
11. H5, control+shift+enter and copy down...
=IFERROR(INDEX(Suppliers,SMALL(IF(FREQUENCY(IF(1-(Suppliers=""),IF(ISNUMBER(DueDates),IF(Functions=$F$1,IF(YEAR(DueDates)=2018,MATCH(Suppliers&"|"&IF(ISNA(Sites),"#",Sites),Suppliers&"|"&IF(ISNA(Sites),"#",Sites),0))))),Ivec),Ivec),ROWS($1:1))),"")
12. I3, control+shift+enter…
=SUM(IF(FREQUENCY(IF(1-(Suppliers=""),IF(ISNUMBER(DueDates),IF(Functions=$F$1,IF(YEAR(DueDates)<=2017,MATCH(Suppliers&"|"&IF(ISNA(Sites),"#",Sites),Suppliers&"|"&IF(ISNA(Sites),"#",Sites),0))))),Ivec),1))
13. I5, control+shift+enter and copy down...
=IFERROR(INDEX(Suppliers,SMALL(IF(FREQUENCY(IF(1-(Suppliers=""),IF(ISNUMBER(DueDates),IF(Functions=$F$1,IF(YEAR(DueDates)<=2017,MATCH(Suppliers&"|"&IF(ISNA(Sites),"#",Sites),Suppliers&"|"&IF(ISNA(Sites),"#",Sites),0))))),Ivec),Ivec),ROWS($1:1))),"")
14. J3, control+shift+enter…
=SUM(IF(FREQUENCY(IF(1-(Suppliers=""),IF(ISNA(DueDates),IF(Functions=$F$1,MATCH(Suppliers&"|"&IF(ISNA(Sites),"#",Sites),Suppliers&"|"&IF(ISNA(Sites),"#",Sites),0)))),Ivec),1))
15. J5, control+shift+enter and copy down...
=IFERROR(INDEX(Suppliers,SMALL(IF(FREQUENCY(IF(1-(Suppliers=""),IF(ISNA(DueDates),IF(Functions=$F$1,MATCH(Suppliers&"|"&IF(ISNA(Sites),"#",Sites),Suppliers&"|"&IF(ISNA(Sites),"#",Sites),0)))),Ivec),Ivec),ROWS($1:1))),"")
Assuming too much and qualifying too much are two faces of the same problem.
Like this thread? Share it with others