3

(This is somewhat similar to How do I reference a cell from within a "...IF"-type function condition string? and SUMIF statement with two different conditions (Text based and checkbox))

I have a formula on one sheet like the following:

=SUMIFS($Amount,$Date,">=01/01/2017",$Date,"<02/01/2017",$Key,"=IN:ALI*")

Where $Amount, $Date, and $Key reference specific columns on another sheet in the same file. This works (yay)

Unfortunately, I have to copy and edit the formula for each column (and across all rows, since each row has a different string at the end as part of the condition). I want to make it easier to copy/paste this formula across multiple columns on the first sheet such that I'd get something like:

column-A: =SUMIFS($Amount,$Date,">=01/01/2017",$Date,"<02/01/2017",$Key,"=IN:ALI*")
column-B: =SUMIFS($Amount,$Date,">=02/01/2017",$Date,"<03/01/2017",$Key,"=IN:ALI*")
...
column-K: =SUMIFS($Amount,$Date,">=11/01/2017",$Date,"<12/01/2017",$Key,"=IN:ALI*")
...

So, I added a row so that near the top of each column (below headers / before data) I entered dates, e.g.:

A3: 01/01/2017
B3: 02/01/2017
...

The plan being to replace the hard-coded strings in my formula with cell refrences (e.g.: A$3, B$3, etc.), but I cannot seem to figure out a way to plug them into the formula in a manner that will allow them to update automatically/correctly when pasted into subsequent columns.

I tried ">=A$3" and "<B$3" - but the 'A' and 'B' don't get updated when the formula is copied from cell A4 to B4.

I tried ">=", A$3 ... "<", B$3 - but I get a syntax error.

I tried ">=" A$3 ... "<" B$3 - but that too gets a syntax error

Is there a way to do this?

(note: If I put all the formulas into a text-editor to do query-replaces on the date strings, I still have to click into each cell to paste them, otherwise the formulas end up as text-strings rather than formulas - if there's a good way around that I'd like to know too)

After various Q&A's and design morphing, this ended up as: AppleScript Numbers: Process data from Quicken

ghoti
  • 53
  • I am a little confused by the point of your last sentence linking to the Apple Discussions page? How does this relate to your question? – Monomeeth Jul 15 '19 at 23:56
  • 3
    I directed most of my questions regarding what I was trying to do to the Apple Discussions forums - and the post includes links to all the prior posted questions I had. It turns out there IS a way to do what I originally wanted: =SUMIFS($Amount,$Date,">=" & A$3,$Date,"<" & B$3,$Key,"=IN:ALI*") The trick being to use '&' to concatenate the comparison operator string and the cell-reference. – ghoti Jul 17 '19 at 15:05
  • In Numbers 12.1 on macOS 11 Big Sur when using an operator within quotes, then the ampersand as a string concatenation operator and then the cell reference in the format LetterNumber works e.g. ">=" & B4 – porg Aug 12 '22 at 17:03
  • The use of '&' is something I learned (later) in Google Sheets [too]. But thanks for following up on this as you never know when someone else will run into a similar issue. – ghoti Aug 13 '22 at 18:11

0 Answers0