Assistance Needed with SUMIF Formula

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

Scanned Documents (Smartsheet).png

Find more posts tagged with

Formulas

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…
Prevent due dates from falling within the same calendar week?
Hi. I am working on a (roughly) annual document review system, in partnership with another team in my organization. They have requested that they receive one document to review per week. But there are more documents to review than there are weeks in the year. So I need to figure out a method for ensuring that I don't send…
How to record date without time
I would like to use automation on a sheet to record the Open and Closed date for a given RAID item. I don't want to record the time, however. Is there a way to exclude a time stamp from the automation? If it is not possible to exclude the time stamp, what formula would I need to use to extract the datae and time into…