Countif(s) problem

Question

Hi all, I am currently trying to use the "countif" function on Excel and am running into some problems with cells that have multiple numbers.

To preface this question, the cells at the top of the page with black numbers are the inputs. The cells at the bottom of the page with red numbers are the outputs of the function.

When the inputs only have one number in the cell, the "outputs are fine, as seen in the "Q1" column at the bottom of the page (the equations being used are countif(b5:b8, 1), countif(b5:b8, 2), countif(b5:b8, 3), countif(b5:b8, 4), and countif(b5:b8, 5), respectively, from top to bottom). However, when moving to the "Q6" column, one input cell has "1,2" and it is not being accounted for in the "Q6" outputs. Is there a function that would count both the 1 and 2 individually?

hansvogelaar · Answer

For example:
=SUM(--ISNUMBER(SEARCH(",1,", ","&amp;B5LB8&amp;",")))
=SUM(--ISNUMBER(SEARCH(",2,", ","&amp;B5LB8&amp;",")))
etc.

peterbartholomew1 · Answer

That looks more straightforward than my solutions!&nbsp; The 'simple' one was= LET(
      textMatches,  COUNTIFS(column, "*"&amp;seq&amp;"*"),
      valueMatches, COUNTIFS(column, seq),
      count, textMatches + valueMatches,
      count
    )To get the whole lot as one spill range (requiring nested arrays)= EVALTHUNKARRλ(BYCOL(data, LAMBDA(column,
    LET(
      textMatches,  COUNTIFS(column, "*"&amp;seq&amp;"*"),
      valueMatches, COUNTIFS(column, seq),
      count, textMatches + valueMatches,
      LAMBDA(count)
    )
  )))COUNTIFS was a problem when it came to picking up both 1, the number, and "1" in a text string.

djclements · Answer

Nice use of thunks, Peter. They are definitely versatile. ;)Working with your sample file, yet another example could be:=LET(
    colϑ, BYCOL(data, LAMBDA(x, LAMBDA(x))),
    crtϑ, BYROW(HSTACK(seq, "*" &amp; seq &amp; "*"), LAMBDA(x, LAMBDA(x))),
    MAP(IFNA(colϑ, crtϑ), IFNA(crtϑ, colϑ), LAMBDA(col,crt, SUM(COUNTIFS(col(), crt()))))
)While this method doesn't involve stacking or indexing, the total number of iterations is significantly higher than yours (rows * columns instead of just columns). With a larger output array, I believe your example would indeed prove to be more efficient.Working with the op's layout, and using Hans' concept, GROUPBY is also a possibility:=LET(
    data, DROP(TRIMRANGE(B4:U24), 1),
    qId, SEQUENCE(, COLUMNS(data)),
    aId, SEQUENCE(, 5),
    grp, TRANSPOSE(GROUPBY(VSTACK("Answer", TOCOL(IFNA(qId, data))), VSTACK(aId, --(ISNUMBER(SEARCH(aId, TOCOL(data))))), SUM, 3, 1)),
    hdr, TAKE(grp, 1),
    VSTACK(IF(ISNUMBER(hdr), "Q" &amp; hdr, hdr), DROP(grp, 1))
)To the best of my knowledge, TRIMRANGE is still only available on the Insider Beta Channel. For testing purposes, I've created my own TRIMRANGEλ custom function in the attached file. Fair warning, though, it is not as robust as what I expect the native function will be, so it should not be used with entire sheet column references.

peterbartholomew1 · Answer

David, I think you are more versatile that I when it comes to building solutions!&nbsp; Though I am coming to recognise some of your more regular tricks such as the IFNA(colϑ, crtϑ).&nbsp; I played which the formulas, firstly to understand them, but also to see whether I could improve the 'readability index'.&nbsp; For example=LET(
    data, TRIMRANGE(DROP(table,1)),
    colϑ, BYCOL(data, THUNK),
    crtϑ, BYROW(HSTACK(seq, "*" &amp; seq &amp; "*"), THUNK),
    rtn, MAP(BCAST(colϑ, crtϑ), BCAST(crtϑ, colϑ), LAMBDA(col,crt, SUM(COUNTIFS(col(), crt())))),
    rtn
)The first line avoids any direct cell references showing on the worksheet.&nbsp; The second and third make the intent clear by using the function THUNK which I usually have lurking in the background.&nbsp; The forth uses a simple wrapper function to rename IFNA to capture the actual intent.&nbsp; Then I changed it and reverted to your older form of broadcastingBCAST = LAMBDA(u, v, IF({1}, u, v));All a case of going round in circles, but it helps me familiarise myself with content.&nbsp;One thing that I have notice in my own work is that, increasingly, thunks are becoming a 'go to' rather than an 'avoid if at all possible'.&nbsp; Gradually all my formulas are climbing into named Lambda functions (a formula is just a function without its clothes on).&nbsp; This is not as apparent on the forum because answers that require digging though several layers of definition are harder to present.&nbsp; The thunk, I think of as a simple reference to a previously calculated array, like a ticket to an item of left luggage.&nbsp; Thunks can and are built into arrays which can be filtered, sorted, searched etc. without having to replicate the content (which may be massive) at each step of the process.&nbsp;&nbsp;Now, all I need is for&nbsp;JoeMcDaid to come up with a native version of EVALTHUNKARRλ and I will be happy.&nbsp; I was pleased with the achievement of using bisection as a way of evaluating arbitrary arrays of thunks efficiently, but it is never going to compete with compiled imperative code that would be the inbuilt version.A version of Excel MAP helper function that will return an array of arrays

sleath01 · Answer

You're a hero!! This worked and you saved me from going down a VERY long rabbit hole! Thank you 😀

Forum Discussion

Countif(s) problem

6 Replies

Resources