Google-sheets – “IF” Function Question

formulasgoogle sheetsgoogle-appsgoogle-sheets-queryworksheet-function

So I have run into an issue…
This maybe simple, but I just can't figure it out.

On a spreadsheet on Google Sheets, I need for a cell to populate a total when a certain cell contains another variable.

So I want the money spent in 2020 row to only pickup the dollar amount in the "Money" column that contains the date 2020 (or 20 for that matter) in the "Date" column.

Thank you in advanced!

Best Answer

With sumifs

=SUMIFS(E2:E10,A2:A10,">31/12/2019",A2:A10,"<01/01/2021")

With If

=sum(arrayformula(If(year(A2:A10)=$J$2,E2:E10,)))

where A is the Date column, E the Money column, and $J$2 is the 2020

Formula

=INT(E4/COUNTBLANK(B2:B))

Explained

The COUNTBLANK formula will count the blank cells, independent of the range and E4 will be divided by it. The INT formula will simply round the value to the nearest integer, since it concerns days.

Example

I've created an example file for you: Count Blank Cells

Google-sheets – How to have a column variable in Google Sheets

I put some random numbers in rows 2 and 3 (instead of times). In cell B4 I wrote this formula: "=B3-B2". To copy the formula to the cells to the right of it, but change the column letter, I just did this:

Select the cell with the formula in it, cell B4.
Click-and-hold on the lower right corner of the cell (not the center).
Drag to the right a few columns.
"Unclick" (let my finger off the mouse).
The formula is automatically copied into each cell in the range, but with a different column letter in each cell.

Note that the totals in row 4 are different. It's the cell value above it, minus the cell value above that. Also note that in the last picture, the formula in cell E4 is "=E3-E2".

You can drag that formula as many columns to the right as you want.

Best Answer

Related Solutions

Google Sheets – Get Average Function for Accumulative Input

Formula

Explained

Example

Google-sheets – How to have a column variable in Google Sheets

Related Topic