2

(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

  • 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 at 23:56
  • 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 at 15:05

You must log in to answer this question.

Browse other questions tagged .