Forum Discussion

mtarler's avatar
mtarler
Silver Contributor
Jun 30, 2020

sandeeptikait  Here is a formula that works:

=SUMPRODUCT(--($B$2:$B$16>=$L$3)*($B$2:$B$16<=$N$3),(--($C$2:$C$16=$K6)+($D$2:$D$16=$K6)+($E$2:$E$16=$K6)+($F$2:$F$16=$K6)+($G$2:$G$16=$K6)+($H$2:$H$16=$K6)))

I wonder if SergeiBaklan  or someone else might chime in with a 'better' answer.

I'm pretty sure some of those new array functions like FILTER could help out here.

Maybe I'll get access to them some day...

 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jun 30, 2020

mtarler , IMHO - "best solution" is quite individual and depends on criterion. From my point of view that's any one which works and which concrete person understands better. Thus using such pattern potentially the person could save a lot of time on deployment and maintenance. That in particular means that for the same person today one solution could be considered as best, and in a while another one, assuming both do exactly the same job. Sorry for some philosophy, with your experience that could be not new.

sandeeptikait , if play with modern Excel functionality, formula could be

=SUM(--(
  INDEX(Range,
     SEQUENCE( COUNTIFS(INDEX(Range,0,1),">="&$L$3,INDEX(Range,0,1),"<="&$N$3),1,2),
     SEQUENCE(1,COLUMNS(Range)-1,2,1)
  )=K6)
)

where the "Range" is named range for entire source data. If extract unique names and return counts as spill it'll be more complicated.

 

Most probably other formula (without Power Query) solutions exist.

Resources