Google Sheets: Compare this month's sales to last month's sales over same number of days

0
votes

I'm sure this is doable and I'm just not finding the solution in the documentation, so big thanks in advance for your help. I want to calculate sales growth month over month.

For example, I'm posting this question on 10/22/2014. Calculating sales thus far for this month is easy, but I also need to know what sales were for the first 22 days of LAST month.

I already have a column containing the values for each day this month, and another column containing the values for each day last month. All I need to do is a way to sum the values for the first 22 days of last month.

Column AH = A list of the dates for last month: 9/1/2014, 9/2/2014...

Column AI = A helper column containing only the DAY of the month of the value in Column AH: 1,2,3,4...

$AJ35 = The day of today's date =DAY(TODAY())

Column AN = The numbers I want to (conditionally) sum

Why won't this formula work?

=SUMIF(AI1:AI34,"<=$AJ35",AN1:AN34)

It calculates a sum of 0.

If I take out the comparison ("<=$AJ35") and manually insert a number, it works fine:

=SUMIF(AI1:AI34,"<=22",AN1:AN34) returns a value of 362, as expected.

1
google-sheetsdate-rangesumifs

1 Answers

0
votes

That is because you have put a cell reference inside a string (surrounded by double quotes)

which means that google is trying to compare against a literal string "$AJ35"
you have to concatenate the cell reference and the operator like so:

=SUMIF(AI1:AI34,"<=" & $AJ35,AN1:AN34)