Reference a column in another sheet

Andy_DAI

Hello - 

My company is new to smartsheets but it has ALREADY made a huge difference for us.

I'm playing with functions that reference other sheets and I have a very specific question - 

I'd like to sum all of a column IF two conditions are met: the dates match and the payment type is the same.  The formula I'm working looks like this:

=SUMIF({Sheet 1 Range 1}, AND({Sheet 1 Range 2} = Date1, {Sheet 1 Range 3} = "CHECK"))

 

Where:

Sheet 1 Range 1 is a column of dollar values being summed

Sheet 1 Range 2 is a column of the deposit dates being compared against Date1

Sheet 1 Range 3 is a column of payment types

 

This is currently returning #INVALID OPERATION, so I'm obviously missing something.

 

Will this work or is there an easier way to do this?



Thanks!

Andy

Paul Newcome

Give this a try.

 

=SUMIFS({Sheet 1 Range 1}, {Sheet 1 Range 2}, @cell = Date1, {Sheet 1 Range 3}, @cell = "CHECK")

Andy_DAI

Thanks, Paul!



I was looking at the @cell function but couldn't really get the syntax based on the examples in the documentation.

Your edits worked perfectly!  Thanks again!

 

Andy

Paul Newcome

It took me a little while and a lot of playing around to get used to the @cell functions. Glad we were able to help.