Forum Discussion

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 ...

Cleaning-Audit.xlsx44 KB

excel

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

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"

ClaudeViret
Copper Contributor
Jul 26, 2021
Detlef_Lewin thanks. tried to place the CLEAN formula, the SUBSTITUTE formula in the COUNTIFS function but it keeps on giving me an error. I've even tried to CRL + ENTER the formula, with no luck.
- Detlef_Lewin
  Silver Contributor
  Jul 26, 2021
  ClaudeViret
  You can't place SUBSITUTE() in COUNTIFS() because COUNTIFS() expects a reference and not an array.
  You have to change from COUNTIFS() to SUMPRODUCT() or use the other options.