How To Select A Part Of A Range, Which Is Output Of A Formula
I seem to be really bad at writing readable titles :-)
Here's the situation:
I have a list of names and values, and I would like to calculate the sum of the values, corresponding to every name:
Name Value a 1 b 2 a 3 c 4 a 5 b 6 c 7
This looks really simple:
for 'a' : =SUMIF($A$2:$A$8,A2,$B$2:$B$8) for 'b' : =SUMIF($A$2:$A$8,A3,$B$2:$B$8) for 'c' : =SUMIF($A$2:$A$8,A4,$B$2:$B$8)
Oops, that's wrong, because A4 does not contain 'c', it contains 'a' again.
No sweat: we can use the
UNIQUE() formula, which puts the values 'a', 'b' and 'c' in column F (
Once we have this, we can easily use this formula:
for 'a' : =SUMIF($A$2:$A$8,F2,$B$2:$B$8) for 'b' : =SUMIF($A$2:$A$8,F3,$B$2:$B$8) for 'c' : =SUMIF($A$2:$A$8,F4,$B$2:$B$8)
This is correct, but it means that I must use the F column as a reference.
I don't want that, I want something like:
for 'a' : =SUMIF($A$2:$A$8,UNIQUE(A2:A8).getElement(1),$B$2:$B$8) for 'b' : =SUMIF($A$2:$A$8,UNIQUE(A2:A8).getElement(2),$B$2:$B$8) for 'c' : =SUMIF($A$2:$A$8,UNIQUE(A2:A8).getElement(3),$B$2:$B$8)
This, obviously, makes no sense, as
getElement() does not exist.
How can I access a part of a
I'm unsure if you want two columns or not, but with ms365:
And to answer the part on the '.GetElement', I'd say that
INDEX() is a very good translation and allows you to slice into an array and pick any of your liking. I don't think it's your goal, but if say you are interested in only the 2nd row of the whole array you can use:
=INDEX(PreviousFormula, 2, 0). The '0' will ensure that we return all column that make up that row and can be left out if the array is only made of a single column in the first place.
- → Trait 'IlluminateFoundationBusDispatchesJobs' not found
- → Laravel save CSV file error
- → Importing large CSV files in MySQL using Laravel
- → VBA how do I edit cell contents based on contents of two other cells in same row
- → How to work with PHPWord and LaravelExcel in Laravel 5.1 framework?
- → How to generate heading for columns?
- → Reading excel file in Laravel
- → Remove Links on Excel after rendered from table2excel.js
- → Create JSON with through Excel Hierarchy
- → How to download fetch response in react as file
- → Laravel Excel big export. Server error 500
- → Download XLSX file on anchor link click in Laravel 5.2