Forum Discussion
Data Filtering by Column/ Horizontal
I have date in 1st column and names in 2nd, 3rd, 4th column and so on. How can I filter a data to get the count of each name in the specified date range. Enclosed excel file with data an...
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...
mtarler Thanks for your response. You solution for counting the number of occurrence is fine. But how would I get the unique records of names, if there are hundreds of names in the list.
sandeeptikait Here is a formula for finding unique names without the new functions (I hope it is ok that it isn't alphabetical):
=IFERROR(OFFSET($A$1,MOD(AGGREGATE(15,7, (ROW($C$3:$H$16)+100000*COLUMN($C$3:$H$16))/(--(COUNTIF(J$5:J10,$C$3:$H$16)=0)), 1),100000)-1,INT(AGGREGATE(15,7, (ROW($C$3:$H$16)+100000*COLUMN($C$3:$H$16))/(--(COUNTIF(J$5:J10,$C$3:$H$16)=0)), 1)/100000)-1),"")