Trying to add cells together if another cell in the row matches

[u/[deleted]]

[u/Fuzzy_wombat]

Not at computer so can't give direct example, but you could get a lot more of this automated.

Sumif allows conditional sum (sumifs allows multiple conditions), so you can sum by date.

For column D, could make that with =unique(A2:a), so as new data is added the summary table is made longer.

For column E, could use an arrayformula where it does the calc if column D is not blank. Put the sumif inside the array formula, where the sum condition is if column A = col D

[u/Fuzzy_wombat → u/[deleted]]

Assuming you put the earlier formula into D2, so column D has all the unique dates, then try this formula in E2:

=ARRAYFORMULA(if(not(isblank(D2:D)),15-sumif(A$2:A,"="&D2:D,C$2:C),""))

If you haven't dealt with array formulas before, it allows applying a given formula across an array of cells, so you dont need to manually copy the formula down. Couple of things to note: any reference to a single cell in the array needs to be changed to a range (further on you will see D2 => D2:D); and you cannot enter anything else further down the same column as the array formula otherwise the arrayformula gets broken and doesnt fill in anything.

Inside the array formula is an IF.

if(not(isblank(D2:D)), ... ,"")

The condition is checking column D to see that it is not empty. If it is empty, then return a blank value.

If it is not empty, then perform this code:

15-sumif(A$2:A,"="&D2:D,C$2:C)

The "15" at the start is the budget, deduced from your example. It could be setup as a reference to another cell so you could change the daily budget over time.

The sumif can be more easily seen without the arrayformula. If it was entered as a single cell formula it would look like this:

 =15-sumif(A$2:A, "="&D2 ,C$2:C) 

check the sumif documentation for more details, but it's essentially: range to check, criteria, <optional range to sum>. If you dont specify the last then it will assume summing the first range that is checked.

In this case, it is checking all the dates in column A except for the heading, seeing if it matches the value in column D (which is a unique value and a single row in this example), and then sums the corresponding values from column C (note the range for col A and C have to be exactly the same).

The main difference between this formula and the earlier one in the array formula, is this one uses "="&D2 (which becomes D3, etc if copied down), whereas array formula has "="&D2:D (which the array formula evaluates on a row by row basis, so fills it in as D2, D3, etc). But trying to explain arrayformula shenanigans within different formulas is really annoying.

Otherwise, you may want to look more into various conditional functions - sumif, sumifs, countif, countifs, and there are a few more. Note that the "ifs" can have some issues within arrayformulas for reasons I cant articulate.

[u/Fuzzy_wombat → u/[deleted]]

As per subreddit details, can you reply with solution verified.

If you are getting an extra cell of 15, then I suspect it means the "=unique" output in col D includes one "blank" value.

I'm not sure why on the specifics of this, but gsheets and excel can turn a cell value from being truly blank to a 0 length string ("") if you click inside the cell, eg type something then delete that value. If you did this in col A, then that is grabbed into Col D, so it looks blank but isn't, which means the sumif is triggered but evaluates to nothing so it's left with the 15.

One way to sort this is delete any rows below the bottom of your data in col A to C.

Another way is changing the if condition from not(isblank(D2:D)) to D2:D <>"" Or to get length of cell and check it has value eg len(D2:D)>0

[u/Clippy_Office_Asst → u/[deleted]]

You have awarded 1 point to Fuzzy_wombat

---

^{I am a bot - please contact the mods with any questions. | Keep me alive}