Error Message with my SUMIF Formula

11/29/20
Accepted

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 MooreKelly Moore ✭✭✭✭✭
    Accepted 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 KhalilBassam Khalil ✭✭✭✭✭
    edited 11/30/20 Accepted 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

    [email protected]

    www.mobilproject.it

    ☑️ 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 MooreKelly Moore ✭✭✭✭✭
    Accepted 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 KhalilBassam Khalil ✭✭✭✭✭
    edited 11/30/20 Accepted 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

    [email protected]

    www.mobilproject.it

    ☑️ 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"

Sign In or Register to comment.