Error Message with my SUMIF Formula

Hi,

I'm attempting to do a simple SUMIF formula, summing up sales per month. This is the formula I'm using: =SUMIF([Contract Date]1:[Contract Date]51, MONTH(@cell) = 10, [Cost of System]1:[Cost of System]51). The Contract Date is the column that has the date and the Cost of System contains the sales figures I want to add up. This example shows me trying to add up sales for October (Month 10). However, I keep getting #INVALID DATA TYPE. Can someone help?

Thank you!

Best Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hi @HML

    Your formula syntax is correct. To verify that I understand correctly, your date column is a column formatted as date column (Column Properties = Date)? The column where your formula resides is formatted as Text/Number column? Also, all of these columns, including the formula column, are on the same single sheet- that is, you're not trying to work with a cross-referenced formula?

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 11/30/20 Answer ✓

    Hi @HML

    To add to KDM's excellent advice/answer. i prepared a sample sheet to present your case please check the following screen shot and the formula, hope this will solve your case, please note that ( Contract Date column is Date type, all other column is text/number type, and use the following formula to calculate :

    1- Month column =MONTH([Contract Date]@row)

    2- Sum Column =SUMIFS([Cost of System]1:[Cost of System]4, MONTH1:MONTH4, criteria5)

    the following screen shot show the result


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hi @HML

    Your formula syntax is correct. To verify that I understand correctly, your date column is a column formatted as date column (Column Properties = Date)? The column where your formula resides is formatted as Text/Number column? Also, all of these columns, including the formula column, are on the same single sheet- that is, you're not trying to work with a cross-referenced formula?

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 11/30/20 Answer ✓

    Hi @HML

    To add to KDM's excellent advice/answer. i prepared a sample sheet to present your case please check the following screen shot and the formula, hope this will solve your case, please note that ( Contract Date column is Date type, all other column is text/number type, and use the following formula to calculate :

    1- Month column =MONTH([Contract Date]@row)

    2- Sum Column =SUMIFS([Cost of System]1:[Cost of System]4, MONTH1:MONTH4, criteria5)

    the following screen shot show the result


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!