Forum Discussion
Summing a column in a filtered range
Hi all,
If i use the FILTER functions and extract 4 (say) columns of data and the third column has numerical values then how to i add the values in the third column. See below.
I know that a formula like SUM(A2#) would work for a single column - but when when data is in the third column there does not seem to be anyway to access it.
I hope this question makes sense - or am i missing something?
many thanks, Peter
| Fiscal year | Project | Local Currency | Cost Type |
| Period 03 2014 | GB1-IS002 | 255.26 | Labour |
| Period 03 2014 | GB1-IS002 | 137.45 | Labour |
| Period 03 2014 | GB1-IS002 | 107.99 | Labour |
| Period 03 2014 | GB1-IS002 | 255.26 | Labour |
| Period 03 2014 | GB1-IS002 | 255.26 | Labour |
| Period 03 2014 | GB1-IS002 | 255.26 | Labour |
| Period 03 2014 | GB1-IS002 | 255.26 | Labour |
| Period 03 2014 | GB1-IS002 | 58.91 | Labour |
| Period 03 2014 | GB1-IS002 | 157.08 | Labour |
| Period 03 2014 | GB1-IS002 | 235.62 | Labour |
| Period 03 2014 | GB1-IS002 | 157.08 | Labour |
| Period 03 2014 | GB1-IS002 | 235.62 | Labour |
| Period 03 2014 | GB1-IS002 | 235.62 | Labour |
6 Replies
The previous solutions are very good, but an alternative based on column name is a double filter as:
=SUM(FILTER(A2#,A1:D1="Local Currency",0))or using XLOOKUP:
=SUM(XLOOKUP("Local Currency",A1:D1,A2#,0))or more simply:
=SUM(A2#*(A1:D1="Local Currency"))
Byethanks for the solutions!
There are couple of ways to do that. You may try these.
=SUM(CHOOSECOLS(A1#,3)) =SUM(INDEX(A1#,,3)) =SUM(INDEX(A1:D14,,3)) =SUM(IFERROR(A1#*{0,0,1,0},0))many thanks!
= SUM(CHOOSECOLS(filteredArray#, 3))
or, combining the two formulas,
= SUM(CHOOSECOLS(FILTER(array, include), 3))
Many thanks!
