Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Problem with SumIF

ArnaudLegrand
edited 12/09/19 in Archived 2017 Posts

Hello @Craig and other Helpers

i experiment the same issue than other

sorry for my english but i m french

i copy your formula below and try to adapt with "laserjet" type

i want to make a sum if a name appear in the row i write it but doesn t work

=SUMIF( [Syndic]1: [Syndic]1132]; FIND("Crouzet"; @cell) > 1; [Total HT]1:[Total HT]1132)

in order to find "Crouzet" wich is a part of name ex "Crouzet Breil"

I m new to Smartsheet et don t understand why

Appriciate help of community

after i will reuse the same formula to make some with different type of  name exemple "foncia", "taboni" but can appeat in row "cabinet Foncia" , or "cabinet Taboni"

regards

return error is #unparseable

legrand@renovcanalisation.com

Capture d’écran 2017-08-14 à 19.09.05.png

Capture d’écran 2017-08-14 à 19.08.57.png

Comments

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Try replacing your semicolons with commas. 

    =SUMIF( [Syndic]1: [Syndic]1132], FIND("Crouzet"; @cell) > 1, [Total HT]1:[Total HT]1132)

  • thank you Mike but doesn t work

    =SUMIF( [Syndic]1: [Syndic]1132], FIND(Crouzet,@cell) > 1, [Total HT]1:[Total HT]1132)

    Capture d’écran 2017-08-16 à 10.54.51.png

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Try removing the brackets off of Syndic like this... brackets should only be used if the column name contains a space. 

    =SUMIF(Syndic1:Syndic1132], FIND("Crouzet",@cell) > 1, [Total HT]1:[Total HT]1132)

    There was also a space character after the first colon. Both of those could have been the issue. Also, the Find function should have "quotes" around the text you are searching for. Let me know if it works now. 

  • hello Mike thank you so much

    but .. doesnt work 

    i think you understand my goal to make sum by name

     

     

     

     

    Capture d’écran 2017-08-17 à 21.06.26.png

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Hi Amaud, I hadn't responded because I had hoped another individual would chime in. I can't seem to get the formula with Find work. As a workaround,

    1. You can create a new column çalled Crouzet and place the find formula =Find("Crouzet", Syndic1) in the first field of that column.

    2. Use the little square in the bottom-right (or double-click it) to fill the formula all the way down)

    2. Hide the column if you don't want to see it in your view.

    3. Then use this formula to sum your =Sumif(crouzet1:crouzet1132, 1, [Total HT]1:[Total HT]1132)

    I was able to get this done in my example. 

    findifWorkaround.jpg

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Essentially, you could create a column for each "Name you are trying to sum and use the same formula to tally those. 

  • thank you but doesnt fit my need 

    i ask the support to help me

    regards

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Update:

    Legrand approached me offline about this and I finally got to it on Aug 22 I'm a bit behind on August threads so am just getting here.

    He shared the sheet with me and I fixed it. Here's what I told him:

    I added this formula:

    =SUMIF(Syndic1:Syndic1122, FIND("CROUZET", @cell) > 1, [Total HT]1:[Total HT]1122)

    to the cell next to your sum in row 1123 as I did not know where to put it.

    Syndic1 points to the [Syndic] column, row 1. 

    Syndic1122 points to the same column, but row 1122.

    If you don't have a row 1122, you will get an error.

    Better is to try to use the whole column like this

    Syndic:Syndic

    instead of 

    Syndic1:Syndic1122

    Also, if using the exact rows, all the ranges need to be the same size.

    This would work

    =SUMIF(Syndic1:Syndic1122, FIND("CROUZET", @cell) > 1, [Total HT]1:[Total HT]1122)

    but this would not

    =SUMIF(Syndic1:Syndic1122, FIND("CROUZET", @cell) > 1, [Total HT]1:[Total HT]999)

    (I changed the last number to 999)

    He said that worked.

    Craig

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    edited 09/05/17

    Gotcha, so it was an issue with the number of rows in his sheet. Thanks for sharing the solution. It looks like the number of rows in his sheet changed from his original post. This is one of the downfalls of community support (not having all the facts). :) I like the range solution without the row numbers. I have started using that more and will advise community members accordingly. I will also try to keep that in mind when community members share formulas with a specific range from the get go. I am glad you were able to assist him and that it now works.

This discussion has been closed.