Google Sheets: count occurrences of a string in different columns -

- 

cola        colb    colc    cold    cole date        id1     id2     id3     id4 1/12/2016   79858   1219         1/15/2016   761     159473       1/19/2016   107597  36204        1/19/2016   109374  93360        1/19/2016           2040         1/19/2016   115902  83366   12617    1/19/2016           7902     1/19/2016   2040    34312        1/19/2016   111954           1/19/2016   2040    116886       1/20/2016   90553   76985        1/20/2016   85454   15933        1/20/2016   88148            1/20/2016   115902  35453   93364    1/20/2016           58459    1/20/2016   49432   112242  75566   154497 1/22/2016   101672  58459        1/22/2016                1/25/2016   1211    2040    39552    1/25/2016           752

i'm trying count number of times id present in range colb:cole if date between timeframe.

with =countif(b2:e,"79858") can count number of times specific id appears in columns ids, can't add date in countifs function because size of arrays different:

=countifs(d2:h,"79858",a2:a,">=19/01/2016",a2:a,"<22/01/2016")  array arguments countifs of different size.

right =countifs(d2:d,"79858",a2:a,">=19/01/2016",a2:a,"<22/01/2016")+countifs(e2:e,"79858",a2:a,">=19/01/2016",a2:a,"<22/01/2016")+countifs(f2:f,"79858",a2:a,">=19/01/2016",a2:a,"<22/01/2016")+countifs(g2:g,"79858",a2:a,">=19/01/2016",a2:a,"<22/01/2016")+countifs(h2:h,"79858",a2:a,">=19/01/2016",a2:a,"<22/01/2016") working, it's resource-heavy.. there less resource-heavy (and elegant) way? arrayformula..?

depending on how numbers formatted (as 'text' or 'numbers')

try:

=countif(filter(b2:e, a2:a>date(2016, 1, 19), a2:a<date(2016, 1, 22)), 79858)

or

=countif(filter(b2:e, a2:a>date(2016, 1, 19), a2:a<date(2016, 1, 22)), "79858")

and see if works ?


Comments

Post a Comment

Popular posts from this blog

Hatching array of circles in AutoCAD using c# -

-
i beginner in autocad plugins .i have created array of circle using following code. now, have hatch circles in array .how that??how hatch circles @ once? moreover, how can return hatch method , use hatch objects.sorry language.thanks in advance. [assembly: commandclass(typeof(beamsection.class1))] namespace beamsection { public class class1 { //class1 obj = new class1(); [commandmethod("beamatsupport")] public void addlightweightpolyline() { // current document , database document acdoc = application.documentmanager.mdiactivedocument; database accurdb = acdoc.database; promptpointresult ppr = acdoc.editor.getpoint("\nselect starting point "); var ucs = acdoc.editor.currentusercoordinatesystem; point3d startingpt = ppr.value.transformby(ucs); list<double> radius = new list<double>(); radius.add(0.5); ...
Read more

ios - UITEXTFIELD InputView Uipicker not working in swift -

-
hello trying pop uipickerview view programmatically when user clicks on textfield. have tried doesn't doing anything. isn't working. nothing happening when click textfield class userprofiletableviewcontroller: uitableviewcontroller,uitextfielddelegate,uipickerviewdelegate,uipickerviewdatasource { var itempicker: uipickerview! = uipickerview() @iboutlet weak var gendertxtfield: uitextfield! var gender = ["male","female"] override func viewdidload() { super.viewdidload() gendertxtfield.delegate = self itempicker!.delegate = self itempicker!.datasource = self itempicker!.backgroundcolor = uicolor.blackcolor() self.gendertxtfield.inputview = itempicker } func numberofcomponentsinpickerview(pickerview: uipickerview) -> int{ return 1 } // returns # of rows in each component.. func pickerview(pickerview: uipickerview, numberofrowsincomponent component:...
Read more