formulasgoogle sheets
I have a Google Sheet of data like this:
4/1/17 | 5
4/1/17 | 7
4/1/17 | 22
4/2/17 | 5
4/2/17 | 10
4/3/17 | 5
4/3/17 | 23
4/3/17 | 1
4/3/17 | 5
4/3/17 | 17
4/3/17 | 3
And I want some sort of formula that I can apply to this range to return this:
4/1/17 | 34
4/2/17 | 15
4/3/17 | 54
(Obviously any date can have any number of entries and it needs to be expandable so that another date can be added – e.g. 4/4/17 – and the "result range" will automatically populate a new row)
Any suggestions?
If you add the following formula in the second sheet (A1).
=QUERY(Sheet1!A1:C9;"SELECT A, SUM(B) WHERE B IS NOT NULL GROUP BY A PIVOT C")
then the table will appear as you want.
Here an explanation of the formula, from the inside to the outside:
PIVOT C transposes the unique results from column C (turning rows into columns)GROUP BY A only filters out unique values in column A. (the same as sheet TABLE!A2)WHERE B IS NOT NULL ignores empty cellsSUM(B) adds the result of the querySELECT A simply displays column A, as unique values If you're only interested in individual results, then I suggest using the following formula:
=IFERROR(SUM(FILTER(DATA!$B$2:B;DATA!$C$2:C=$B$1;DATA!$A$2:A=A2));"")
Here an explanation of the formula, from the inside to the outside:
FILTER function will retrieve the amounts for range B2:B, column B without the header, by filtering range B2:B for "No" and range C2:C for "Yes".SUM function will add them together.IFERROR will leave a blank cell if an error occurred (not result).
See example file I've prepared, where both samples are present: SUM data based on two variables
The formula in the example file is a bit different (the range is set to contain complete columns).
I found three solutions for you.
=INDIRECT("DATA!A"&A1)
function dataSubset(data,subset) {
var output = [];
for(var i=0, iLen=subset.length; i<iLen; i++) {
for(var j=1, jLen=data.length+1; j<jLen; j++) {
if(parseInt(subset[i]) == parseInt(j)) {
output.push(data[j-1]);
}
}
}
return output;
}
=ARRAY_ROW(INDIRECT("DATA!A"&A1),INDIRECT("DATA!B"&A1))
I've prepared two example files for you:
Best Answer
If your data is in ColumnsA:B then your first date in say D1 and in E1:
is not fully automatic (you would have to drag D1:E1 down to suit) but should give the desired output. Provided dragged far enough further data added to B:C should update D:E.