Sumif, index/match and iferror help

Alicia L

I am attempting a formula to sum specific cells from a column from a reference sheet if the Shift column from the origin sheet = 1st and the dates match on both sheets and a return result of 0 if there is not a match, but I'm not getting the arguments correct.

#INCORRECT ARGUMENT SET

I've tried this as well.

#UNPARSEABLE

To summarize - If the reference sheet column Shift = 1st, then sum those values that match the row dates from the reference sheet and the sheet the formula is being entered into. Return a result of 0 if there is not a match.

Hollie Green

You need to add a ,1 before your final )

Alicia L

Adding the ,1 removed the error, but did not produce a return of the text I needed, so I added a second reference point in the formula and that worked.

Kerry St. Thomas

If I'm understanding correctly, you're trying to total "Untreated" where the date on the source sheet is the date on the current sheet row and the shift on the source sheet is "1st."

If that's accurate, I think you're complicating matters with the Index/Match. You can do what I'm describing using SUMIFS. Note the plural - so the difference between this and your SUMIF is essentially "the sum if this and if that and if the other and so on" multiple criteria are met, instead of looking for just one criterion.

=SUMIFS({Untreated}, {Date}, Date@row, {Shift}, "1st")

More on the SUMIFS function:
https://help.smartsheet.com/function/sumifs

Good luck!

Alicia L

This formula worked, thank you. =SUMIFS({Untreated}, {Date}, Date@row, {Shift}, "1st")

How do I adjust the same formula to return a comment(words) from another sheet? I still need all these cross references {Date}, Date@row, {Shift}, "1st", but instead of a sum from the {Untreated} reference column, I need a return value or the words from a {End of Shift Comments} column.

I tried an Index and an Index/Match, instead of Sumifs, but my formula was incorrect.

Kerry St. Thomas

INDEX/MATCH finds a data point. A single cell. SUMIF and SUMIFS evaluates an entire data range. Plural data points. As such, I tend to confuse myself if I try to merge the two logic types.

Adding to SUMIFS is fairly straightforward: the first clause in the formula is what you're adding; each pair of items after that is the different criteria you're searching for (and where you're searching). Just keep adding to the existing formula.

An alternate to SUMIFS is SUM(COLLECT( «something to evaluate» ))

More on formulas - for info about SUM(COLLECT()) see the collect info and see how it's used with another arithmetic function, AVG.

https://help.smartsheet.com/function/sumifs
https://help.smartsheet.com/function/collect

Good luck!

Alicia L

This is the error that I get - #INCORRECT ARGUMENT SET

I need to copy the text from one cell in the reference sheet based on the dates matching and the word "1st". The cell I need to copy from may change from day to day. I do not want to return a numerical value. I tried this index/collect formula and index/match, neither worked.

Hollie Green

You need to add a ,1 before your final )

Alicia L

Adding the ,1 removed the error, but did not produce a return of the text I needed, so I added a second reference point in the formula and that worked.