SOLVED

Sum two cells by a value per each month in a single cell

Copper Contributor

I've not yet found an article that sums in a single cell for each month. The only ones I've read put the dates on separate rows, but that isn't what I'm trying to do. Hopefully, there is a formula that will enable this...

 

I have a cell with a value of 17, let's say that is in A1.

In A2, I want to test for the current month and give it a value, e.g., Jan is 2, Feb is 4, Mar is 6, etc.

Then, in A2, I want to sum the current months value + 17 (value in A1).

 

Like I mentioned, I've had a hard time finding this formula, as all I've seen puts a date on multiple rows.

 

Thanks in advance!

9 Replies

@rcortesim 

That doesn't work, you need another cell(s) of formulae which defines values for months, with that sum it with A1 placing result in A2.

Using something like Month(Now())? Then, take that value + value of A1? It can't be that easy, can it?
best response confirmed by rcortesim (Copper Contributor)
Solution

@rcortesim 

If cell contains user input it is illegal to also have a formula because that would change the input.  If the month were derived from a formula, then something of the form

= input + 2*MONTH(TODAY())

is possible ('input' here referring to the content of cell A1).

No, I would have to give the Month(Now()) a value for each month that is returned, e.g.,
if Jan, then value is 2.
if Feb, then value is 4
etc.
Finally, I'd have to add that value to value of A1.
Sorry Peter, I was responding to my own post.
Not sure I understand 2*MONTH(TODAY())
The month function returns an integer between 1 and 12. Your example happened to add a number that was double the month number in each case. More general values could be returned by index from a reference array.
That part I get, but (TODAY()) is needed, too?
Thank you Peter. It turns out to be a quite simple function, but I needed de-mystifying. Sometimes, I default to embedding tests to get a value, which clearly isn't needed in this case.
1 best response

Accepted Solutions
best response confirmed by rcortesim (Copper Contributor)
Solution

@rcortesim 

If cell contains user input it is illegal to also have a formula because that would change the input.  If the month were derived from a formula, then something of the form

= input + 2*MONTH(TODAY())

is possible ('input' here referring to the content of cell A1).

View solution in original post