Excel spillrange with sum

Question

SUM(FILTER(Tbl_Assets[WDV on Year 0],(Tbl_Assets[Asset Group as per Co. Act (SCH II)]=Q14)*((Tbl_Assets[Disposal Date]>StartDate)+(Tbl_Assets[Disposal Date]=0)),0))

In the above formula, if I try to input a range Q14:Q50, instead of Q14, it is giving #N/A error. How to work around this.

Column Q has following values of Asset Group

Buildings Plant & Machinery Furniture etc.

From your current post it's unclear what is supposed to be in range Q14:Q50. Can you edit in some sample data, using markdown table and show us the desired result. — Excellor
– Excellor, Commented Apr 18 at 14:16
You probably want to integrate COUNTIF() into that formula as the way you explain the required test against an range of cells (instead of a singular cell) does not work directly in this syntax. — JvdV
– JvdV, Commented Apr 18 at 14:29
@JvdV I need sum of values matching the criteria, hence counif will not help. — Deepak Sugandhi
– Deepak Sugandhi, Commented Apr 18 at 14:37
Then you dont understand my comment. You have a range of criteria instead of a single cell. One way to deal with this is to include what I mentioned in the formula. — JvdV
– JvdV, Commented Apr 18 at 15:36
@Deepak If you compare 2 column arrays as in Tbl_Assets[Asset Group...]=Q14:Q50 (part of the 2nd argument of your FILTER) you get a result which is a column array with size equal to the larger of the two compared arrays. The n'th element of the first array (n=1,2,...) is compared to the n'th element of the second array and the result is assigned to the n'th element of the result. Where n exceeds the size of the smaller array the result is #N/A. Unless your table has exactly 37 rows the FILTER will fail and even when it does have 37 rows it is probably not the filter you want. — DMM
– DMM, Commented Apr 19 at 16:24

barry houdini · Accepted Answer · 2025-04-18 15:26:30Z

2

Try using ISNUMBER(MATCH in the relevant part of the formula, i.e.

SUM(FILTER(Tbl_Assets[WDV on Year 0],ISNUMBER(MATCH(Tbl_Assets[Asset Group as per Co. Act (SCH II)],Q14:Q50,0))*((Tbl_Assets[Disposal Date]>StartDate)+(Tbl_Assets[Disposal Date]=0)),0))

answered Apr 18 at 15:26

barry houdini

46.9k8 gold badges67 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Deepak Sugandhi Apr 19 at 5:03

SUM(FILTER(Tbl_Assets[WDV on Year 0],(ISNUMBER(MATCH(Tbl_Assets[Asset Group as per Co. Act (SCH II)],Q14:Q50,0)))*((Tbl_Assets[Disposal Date]>StartDate)+(Tbl_Assets[Disposal Date]=0)),0)) This is still not working

barry houdini Apr 19 at 5:15

How is it not working - do you get an error or the wrong result?

Deepak Saini Apr 21 at 7:56

This is exactly what I would do. Are you getting an error ?

Deepak Sugandhi Apr 21 at 10:37

@barryhoudini Output is single cell even if reference of Q14:Q50 is given.

Deepak Sugandhi · Accepted Answer · 2025-04-22 05:03:16Z

-1

Solved by wrapping the same formula inside BYROW.

answered Apr 22 at 5:03

Deepak Sugandhi

315 bronze badges

Collectives™ on Stack Overflow

Excel spillrange with sum

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related