Forum Discussion

ClaudeViret's avatar
ClaudeViret
Copper Contributor
Jul 26, 2021

Convert Array formula

Hi there, 

 

I'm trying to insert a formula (COUNTIFS) from a table. I've done this countless times with no issue. The array looks different when I try to get the criteria from the table. It seems like the end has whitespace or line break or something that's preventing the calculation. I have attached a file for you to have a look at it with screenshots. See how the formula differs from the "typed" data in column K. Column K returns the correct answer (4) while the other two returns the incorrect answer (0). Column J was a copy and paste from the table (Sheet: Form1)

 

Please assist. 

excel

10 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Jul 29, 2021

    ClaudeViret 

    See the example inserted in the Excel file.

     

    =SUMPRODUCT(--(EXACT(J8:J12,D1)))

     

    for the Sheet "Form1" 

    =SUMPRODUCT(--(EXACT(Form1!E2:E6,D1)))

     

     

    I would be happy to know if I could help.

     

    Nikolino

    I know I don't know anything (Socrates)

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jul 26, 2021

    ClaudeViret 

    This was a bit of a battle, but I converted the output to a table so that the single-cell array formula propagates down the column.  For the first row, I introduced a single character wildcard that hoped would match the line-feed.

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Jul 26, 2021

      The formula reads

      = LET(
   Club, AppInput[Choose Club],
   Date, AppInput[Completion time],
   Field, XLOOKUP(@category, AppInput[#Headers], AppInput),
   chr, IF(@category=AppInput[[#Headers],[Outside of club, entrance, grounds/parking]],"?",""),
   COUNTIFS(
      Field, Setting&chr,
      Club, selectedClub,
      Date, ">="&RequiredDate,
      Date, "<"&RequiredDate+1) )

      [I should have included it above but editing a post tends to generate a mess on this forum]

      • ClaudeViret's avatar
        ClaudeViret
        Copper Contributor
        Jul 29, 2021
        Thanks, but I cannot follow your formula. Did you use cell/range references?
    • ClaudeViret's avatar
      ClaudeViret
      Copper Contributor
      Jul 26, 2021

      Detlef_LewinThanks. But I can't do that.

       

      Is there a way to remove them with a formula or workaround? The table is input from office forms and I need to create a formula or something to read the data so that it can generate monthly reports

      • Detlef_Lewin's avatar
        Detlef_Lewin
        Silver Contributor
        Jul 26, 2021

        ClaudeViret 

        You should investigate the source. It is strange that Forms sometimes would add a line feed.

         

        You could use Find & Replace, SUBSTITUTE(), Power Query ...

         

        let
    Source = Excel.CurrentWorkbook(){[Name="AppInput"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID", "Start time", "Completion time", "Name"}, "Question", "Answer"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","#(lf)","",Replacer.ReplaceText,{"Answer"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Start time", type datetime}, {"Completion time", type datetime}, {"ID", Int64.Type}, {"Name", type text}})
in
    #"Changed Type"

        *

Resources