simple sumif formula not working

Sarah_lee123

Hello,

I am doing a cross sheet formula with a SUMIF.

=SUMIF({CLONE BATCH ID}, [CLONE BATCH ID]@row, {CLONE CUT QTY})

I am repeating the formula I have used in other sheets. I re-watched the formulas video and am doing everything exactly a I should. Why am I still getting a zero for my answer. It is returning the number zero and not giving me an error message.

Thoughts?

Thanks

Paul Newcome

That means there is some kind of issue with your clone batch id. Are you able to provide some screenshots for reference? How are the IDs structured?

Sarah_lee123

Sarah_lee123

The Batch ID's will come in as a barcode scan, but I believe I hand entered these. They are pulled in using a helper column to condense the batch name as discussed in previous questions I posed to the community. I wonder if there is an issue with the way I condensed the batch numbers into the sheet I am trying to place the SUMIF equation.

I am trying to move from the "Cut Tracking" sheet - (cuts per plant), to the "Batch Tracking" sheet (Cuts per batch made up of multiple plants)

Sarah_lee123

I used the exact same functions in this OTHER batch tracking sheet and it worked fine. Seems like I am doing thing identical in both situations, but I am not getting what I'm looking for.

Paul Newcome

Ok. Let's try something here... Copy that first Clone Batch ID in the metrics sheet, right click on an empty cell, paste special, values.

Double click into this. Is there an apostrophe there? Is there an apostrophe in the source sheet to allow for leading zeros?

Genevieve P.

Hiya!

Jumping on this thread because any time I see a formula using matching across sheets, but the matching value has a leading 0... the first thing I'd try is adding an "@cell = " before the value in case this clears up the issue.

E.g.:

=SUMIF({CLONE BATCH ID}, @cell = [CLONE BATCH ID]@row, {CLONE CUT QTY})

Let us know if this worked?

Paul Newcome

@Genevieve P. Thanks for the input. I never think of that because I always use "@cell", so I don't run into that particular issue.

Sarah_lee123

@Paul Newcome

They do have apostrophes and it still wasn't working

@Genevieve P.

Worked like a charm.

Thanks again guys!!

Paul Newcome

@Sarah_lee123 Long story short the apostrophes were why it wasn't working. But @Genevieve P. swooped in to save the day with a piece that I always forget about.

Genevieve P.

https://community.smartsheet.com/discussion/comment/330807#Comment_330807

Teamwork!  🙂



Sarah_lee123

Sarah_lee123

Having trouble with my formulas all of a sudden. This formula also stopped working for me. Is the program glitchy? am I confusing the system with all my different cell references? Very confused right now. These same formulas work fine in other sheets, but then don't work in this sheet.

Source sheet

Paul Newcome

@Sarah_lee123 It may be that in one you are using SUMIFS and in another you are using SUMIF (without the "S" on the end). They have different syntax depending on which one you are using. In the one with the "S", the range to sum comes first and is then followed by your range/criteria sets for filtering the data. In the one without, the filtering range/criteria set comes first and the last portion is the range to sum.

I personally ALWAYS use the one with the "S" for a couple of reasons. First it can be used with a single range/criteria set but they syntax doesn't change if you find you need to add more range/criteria sets to it. Second... It just reads a little more logically in my opinion.

Having said all that... It looks like maybe you need to just swap the ranges in your posted formula.