1. Forum
  2. Usenet
  3. MICROSOFT.PUBLIC.EXCEL

  • Spaces in a formula

    From Ross Dennis@21:1/5 to All on Wed Aug 17 08:14:54 2022
    I need to put "search exactly" in my formula, which works fine, as per this =SUMIFS(N:N,J:J,59,AE:AE,"31/07/2022",D:D,"Internal")
    However one of the fields I need to report on is "Retail AG" and if I do =SUMIFS(N:N,J:J,59,AE:AE,"31/07/2022",D:D,"Retail AG") it gives me zero, which I am pretty sure is down to the space in the "Retail AG"- how can I make it read this info?
    NB- I do not have the master data to change the name of the cell to not include a space.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Claus Busch@21:1/5 to All on Wed Aug 17 17:54:46 2022
    Hi Ross.

    Am Wed, 17 Aug 2022 08:14:54 -0700 (PDT) schrieb Ross Dennis:

    I need to put "search exactly" in my formula, which works fine, as per this =SUMIFS(N:N,J:J,59,AE:AE,"31/07/2022",D:D,"Internal")
    However one of the fields I need to report on is "Retail AG" and if I do =SUMIFS(N:N,J:J,59,AE:AE,"31/07/2022",D:D,"Retail AG") it gives me zero, which I am pretty sure is down to the space in the "Retail AG"- how can I make it read this info?
    NB- I do not have the master data to change the name of the cell to not include a space.

    I guess you have leading or trailing spaces in column D.
    Try:
    =SUMPRODUCT(N:N*(J:J=59)*(AE:AE=DATE(2022,7,31))*(TRIM(D:D)="Retail AG"))


    Regards
    Claus B.
    --
    Windows10
    Microsoft 365 for business

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Ross Dennis@21:1/5 to claus...@t-online.de on Thu Aug 18 00:07:32 2022
    On Wednesday, 17 August 2022 at 16:54:52 UTC+1, claus...@t-online.de wrote:
    Hi Ross.
    Am Wed, 17 Aug 2022 08:14:54 -0700 (PDT) schrieb Ross Dennis:

    I need to put "search exactly" in my formula, which works fine, as per this =SUMIFS(N:N,J:J,59,AE:AE,"31/07/2022",D:D,"Internal")
    However one of the fields I need to report on is "Retail AG" and if I do =SUMIFS(N:N,J:J,59,AE:AE,"31/07/2022",D:D,"Retail AG") it gives me zero, which I am pretty sure is down to the space in the "Retail AG"- how can I make it read this info?
    NB- I do not have the master data to change the name of the cell to not include a space.
    I guess you have leading or trailing spaces in column D.
    Try:
    =SUMPRODUCT(N:N*(J:J=59)*(AE:AE=DATE(2022,7,31))*(TRIM(D:D)="Retail AG"))


    Regards
    Claus B.
    --
    Windows10
    Microsoft 365 for business
    Alas, this gives me a #VALUE error. Any other suggestions welcomed :)

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)