Simple SumIf Not Working

Shelby Wilder · 06/07/21

I am attempting a simple sumif function and for some reason it constantly reads "INVALID." Below is a similar premise to eliminate wordiness.

I want the formula to look at the PRODUCT column and sum all the PRICE rows that contain the same PRODUCT, (i.e add all the prices of toys sold.) I am wanting this formula to be included on each row, hence the @row. The formula I am using is:

=SUMIF(Product:Product, Product@Row, Price:Price)

Product Price

TOY 12.00

TOY 13.00

Coat 15.00

The ACTUAL formula I am using is: =SUMIF(FabricColor:FabricColor, FabricColor@row, Balance:Balance)

Thanks!

Bassam Khalil · 06/07/21

Hi @Shelby Wilder

Hope you are fine, if i understand what you mean please try the following formula:

=SUMIFS(PRICE:PRICE, PRODUCT:PRODUCT, PRODUCT@row)

Shelby Wilder · 06/07/21

https://community.smartsheet.com/discussion/comment/294023#Comment_294023

@Bassam Khalil

I changed the formula to =SUMIF(Balance:Balance, FabricColor:FabricColor, FabricColor@row), unfortunately it does not work and reads invalid.

Bassam Khalil · 06/07/21

@Shelby Wilder

It's SUMIFS not SUMIF, please check the following:

=SUMIFS(Balance:Balance, FabricColor:FabricColor, FabricColor@row)

Shelby Wilder · 06/07/21

https://community.smartsheet.com/discussion/comment/294023#Comment_294023

@Bassam Khalil

I changed the formula to =SUMIFS(Balance:Balance, FabricColor:FabricColor, FabricColor@row), and still reads as invalid, unfortunately.

Bassam Khalil · 06/07/21

@Shelby Wilder

Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Shelby Wilder · 06/07/21

@Bassam Khalil =SUMIFS(Balance:Balance, FabricColor:FabricColor, FabricColor@row) also is reading invalid operation as well. Thank you

Genevieve P. · 06/16/21

Hi @Shelby Wilder

I agree with @Bassam Khalil, it would be easier to help if we could see a screen capture of your sheet (but please block out any sensitive data).

Sometimes this error can occur if there's another formula error in any of the columns you're referencing. Are you using a formula in either the Balance or FabricColour columns? If so, is there any cell in the entire range that has a formula error?

Follow up · 01/24/23

Hi @Genevieve P. , Im having the same error as her:

=SUMIFS([Duration Completed]:[Duration Completed], Meeting:Meeting, Meeting@row, [Start Date]:[Start Date], [Start Date]@row,[Assigned To]:[Assigned To], [Assigned To]@row)

I tried this to excel and the formula i used works without any error. and its the same as this:

Genevieve P. · 01/25/23

Hi @Follow up

Two questions:

What type of column are you putting the formula in?
What is the formula in the [Duration Completed] column?

Simple SumIf Not Working

Answers

Help Article Resources

Categories