Assistance Needed with SUMIF Formula

Nikhil Chawla

Hello,

I need assistance with a few sumif formulas. 

1) I need to calculate Documents Scanned This Month and Documents Scanned Last Month. The formula should look for the word Documents in the column called "Scanning Queue" and give me the total Scanned Quantity for both current month and last month. Below is the formula I am using for the two calculations but I am receiving error #UNPARSEABLE.

• Documents Scanned This Month: =SUMIFS(Date:Date, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), Date:Date, IFERROR(YEAR(@cell), 0) = YEAR(TODAY())), [Scanning Queue]:[Scanning Queue], FIND("Documents", @cell) > 0, [Scanned Quantity]:[Scanned Quantity]) 
• Documents Scanned Last Month: =IF(MONTH(TODAY()) = 1, SUMIFS(Date:Date, IFERROR(MONTH(@cell), 0) = 12, Date:Date, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1),[Scanning Queue]:[Scanning Queue], FIND("Documents", @cell) > 0, [Scanned Quantity]:[Scanned Quantity]), SUMIFS(Date:Date, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()) - 1, Date:Date, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), [Scanning Queue]:[Scanning Queue], FIND("Documents", @cell) > 0, [Scanned Quantity]:[Scanned Quantity])

2) I also want to calculate how many CON - Documents and CON - eFlows were scanned this month and last month. I currently don't have a working formula for this yet.

 

Regards,

Nikhil Chawla

Genevieve P.

Hello Nikhil,

You’re off to a great start, but your formulas just need a bit of rearranging. With SUMIFS, plural, it requires the column with the values you want to add up, first, and then the criteria. 

I’ve broken down the formula into each instruction. First, “Scanned Quantity” is the column I want it to Sum. Then, there are the three conditions: that the Year is the same as today, that the Month is the same as today, and that the cells in the column “Scanning Queue” contain the word “Documents” somewhere in it:

=SUMIFS([Scanned Quantity]:[Scanned Quantity], 

Date:Date, YEAR(@cell) = YEAR(TODAY()), 

Date:Date, MONTH(@cell) = MONTH(TODAY()),

[Scanning Queue]:[Scanning Queue], CONTAINS("Documents", @cell))

 

 

This creates a total formula of:

=SUMIFS([Scanned Quantity]:[Scanned Quantity], Date:Date, YEAR(@cell) = YEAR(TODAY()), Date:Date, MONTH(@cell) = MONTH(TODAY()), [Scanning Queue]:[Scanning Queue], CONTAINS("Documents", @cell))

 

I didn’t use the IFERROR function in my formula example; if you need that in there, it would be useful to see more of your sheet and understand where you are seeing errors in the Date column.

Now that you understand a bit more about how SUMIFS works, you may be able to sort out the other formulas on your own. For example, the CON formula can search for if the Scanning Queue CONTAINS “CON” instead of looking for “Documents.” 

If you are still coming up against an error, let us know! 

 

Cheers,

Genevieve

Eric M Oliveira

Hi Nikhil,

 

To add to Genevieve's response, she is absolutely correct, it appears the SUMIFS function is missing the sum range from the formula.

 

SUMIFS is further outlined here: https://help.smartsheet.com/function/sumifs

 

Two additional adjustments you may want to consider when creating the above-stated formula.

 

1. If you utilize the HAS function instead of the FIND function this will allow you to search for an exact match of the desired text value. Both, however, do work. This is further outlined here: https://help.smartsheet.com/function/has

 

2. If you're receiving an error when utilizing Date columns this is often because within the data range there is a black cell in a row that contains text. Instead of utilizing an IFERROR I would recommend filling in the blank cell with a date. That way, if there is an error with the formula and it's producing an inaccurate calculation its not being covered up by the IFERROR.

 

Hope this helps. Have a wonderful day.

 

Eric

 

Smartsheet Technical Support