SUM CELLS (not adjacent) and some have #N/A

[MixedUpExcel]

Hi,

I have multiple Rows (within a Column) that I want to SUM but some have #N/A in them.

When I have the formula to create the Total, I want to be able to drag it across 24 Columns.

I have found 2 examples - both work in various situations but not my specific one.

Can anyone suggest a working solution that I can create in the first Column and then drag to the right?

=SUMIF(C69:C112,"<>#N/A")

- Works if I need to SUM up all values in the Column (but in this case I just need to SUM up the PACK Values)

[COLOR=#333333]=SUM(SUMIF(INDIRECT({"D4","E6","F8","G10","H12","I14","J16","K18","L20","M22","N24","O26"}),"<>#N/A"))[/COLOR]

- Would work but I can't select the Cells as they are in the curly brackets (array) so I can't then drag them across all the other Columns

Example Table:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]2017
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]JAN[/TD]
[TD]FEB[/TD]
[TD]MAR[/TD]
[TD]APR[/TD]
[TD]MAY[/TD]
[TD]JUN[/TD]
[TD]JUL[/TD]
[TD]AUG[/TD]
[TD]SEPT[/TD]
[TD]OCT[/TD]
[TD]NOV[/TD]
[TD]DEC[/TD]
[TD]JAN[/TD]
[TD]FEB[/TD]
[TD]MAR[/TD]
[TD]APR[/TD]
[TD]MAY[/TD]
[TD]JUN[/TD]
[TD]JUL[/TD]
[TD]AUG[/TD]
[TD]SEP[/TD]
[TD]OCT[/TD]
[TD]NOV[/TD]
[/TR]
[TR]
[TD]Pack 1[/TD]
[TD]250[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Component 1[/TD]
[TD]VALUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Component 2[/TD]
[TD]VALUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pack 2[/TD]
[TD]175[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Component 1[/TD]
[TD]VALUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Component 2[/TD]
[TD]VALUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Component 3[/TD]
[TD]VALUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pack [/TD]
[TD]93[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Component 1[/TD]
[TD]VALUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Component 2[/TD]
[TD]VALUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Component 3[/TD]
[TD]VALUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Component 4[/TD]
[TD]VALUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Component 5[/TD]
[TD]VALUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pack [/TD]
[TD]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Component 1[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Component 2[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Component 3[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pack [/TD]
[TD]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Component 1[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Component 2[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Component 3[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Component 4[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pack [/TD]
[TD]374[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Component 1[/TD]
[TD]VALUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Component 2[/TD]
[TD]VALUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Component 3[/TD]
[TD]VALUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Component 4[/TD]
[TD]VALUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pack [/TD]
[TD]187[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Component 1[/TD]
[TD]VALUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Component 2[/TD]
[TD]VALUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Component 3[/TD]
[TD]VALUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pack [/TD]
[TD]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Component 1[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Component 2[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Component 3[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Component 4[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Component 5[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PACK TOTAL[/TD]
[TD]1079[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance.

Simon

 

[MixedUpExcel]

but @cunningAce's suggestion in post#2 works,

Hi Alan,

It only works if I have the word Pack in that Column, but I won't.

Packs - these are unique names, Pack is just to illustrate that my Pack Name is against the Value I want to SUM up
Component - these will also be unique name and are what make up the value of the Pack
VALUE - this represents differing values, unless it's #N/A but I don't want to SUM up the various values against the Components, only against the PACKS.

Packs was just for illustration - please replace each instance of PACK with another word... eg. BMW / MERCEDES / VAUXHALL / RENAULT etc....

Thanks.

 

[AlanY]

like this?

Excel 2013/2016
	A	B	C	D
1		2017
2		JAN	FEB	MAR
3	BMW 1	250
4	Component 1	130
5	Component 2	120
6	BMW 2	175
7	Component 1	75
8	Component 2	60
9	Component 3	40
10	BMW	93
11	Component 1	63
12	Component 2	10
13	Component 3	10
14	Component 4	6
15	Component 5	4
16	BMW	#N/A
17	Component 1	#N/A
18	Component 2	#N/A
19	Component 3	#N/A
20	BMW	#N/A
21	Component 1	#N/A
22	Component 2	#N/A
23	Component 3	#N/A
24	Component 4	#N/A
25	BMW	374
26	Component 1	150
27	Component 2	120
28	Component 3	50
29	Component 4	54
30	BMW	187
31	Component 1	100
32	Component 2	47
33	Component 3	40
34	BMW	#N/A
35	Component 1	#N/A
36	Component 2	#N/A
37	Component 3	#N/A
38	Component 4	#N/A
39	Component 5	#N/A
40	BMW TOTAL	1079
Sheet2

Cell Formulas
Range	Formula
B40	{=SUMIFS(B3:B39,A3:A39,"BMW*",B3:B39,"<>#N/A")}
Press CTRL+SHIFT+ENTER to enter array formulas.

or?

Excel 2013/2016
	A	B	C	D
1		2017
2		JAN	FEB	MAR
3	BMW 1	250
4	Component 1	130
5	Component 2	120
6	ABC	175
7	Component 1	75
8	Component 2	60
9	Component 3	40
10	BMW	93
11	Component 1	63
12	Component 2	10
13	Component 3	10
14	Component 4	6
15	Component 5	4
16	XYZ	#N/A
17	Component 1	#N/A
18	Component 2	#N/A
19	Component 3	#N/A
20	BMW	#N/A
21	Component 1	#N/A
22	Component 2	#N/A
23	Component 3	#N/A
24	Component 4	#N/A
25	BMW	374
26	Component 1	150
27	Component 2	120
28	Component 3	50
29	Component 4	54
30	BMW	187
31	Component 1	100
32	Component 2	47
33	Component 3	40
34	BMW	#N/A
35	Component 1	#N/A
36	Component 2	#N/A
37	Component 3	#N/A
38	Component 4	#N/A
39	Component 5	#N/A
40	BMW TOTAL
Sheet2

 

[AlanY]

again, as per @cunningAce's suggestion, will that work for you?

Excel 2013/2016
	A	B	C	D
1		Helper	2017
2			JAN	FEB
3	Pack	X	250
4	Component 1		130
5	Component 2		120
6	BMW 2	X	175
7	Component 1		75
8	Component 2		60
9	Component 3		40
10	BMW	X	93
11	Component 1		63
12	Component 2		10
13	Component 3		10
14	Component 4		6
15	Component 5		4
16	BMW	X	#N/A
17	Component 1		#N/A
18	Component 2		#N/A
19	Component 3		#N/A
20	BMW	X	#N/A
21	Component 1		#N/A
22	Component 2		#N/A
23	Component 3		#N/A
24	Component 4		#N/A
25	BMW	X	374
26	Component 1		150
27	Component 2		120
28	Component 3		50
29	Component 4		54
30	BMW	X	187
31	Component 1		100
32	Component 2		47
33	Component 3		40
34	BMW	X	#N/A
35	Component 1		#N/A
36	Component 2		#N/A
37	Component 3		#N/A
38	Component 4		#N/A
39	Component 5		#N/A
40	BMW TOTAL		1079
Sheet2

Cell Formulas
Range	Formula
C40	=SUMIFS(C3:C39,B3:B39,"X",C3:C39,"<>#N/A")

 

[MixedUpExcel]

Hi both,

It appears I've been explaining myself very badly.

I've since created a formula that works.. but it's not pretty.

Is there a way to simplify it / stream line it into a SUMIFS? This is a real example of the formula I've not got working and I'm able to drag it across the row with the totals.

=SUMIF(C15,"<>#N/A")+SUMIF(C21,"<>#N/A")+SUMIF(C27,"<>#N/A")+SUMIF(C28,"<>#N/A")+SUMIF(C34,"<>#N/A")+SUMIF(C40,"<>#N/A")+SUMIF(C46,"<>#N/A")+SUMIF(C52,"<>#N/A")+SUMIF(C58,"<>#N/A")

Thanks again for your time on this.

S.

 

[cunningAce]

You will struggle without someway of determining what rows are 'Pack' values?
I think you're only option is for an additional column, stating Pack or Component.

 

[Eric W]

How about something like:

=SUM(IFERROR(SUBTOTAL(104,OFFSET(C1,{15,21,27,28,34,40,46,52,58}-1,)),0))

confirmed with Control+Shift+Enter.

The biggest issue is with the disjoint cells. If they were contiguous, you could just use AGGREGATE. But the formula above will sum up C15, C21, etc., and if you drag the formula to the right, the C1 will change to D1, and it will sum up D15, D21, etc. If you have cells in multiple columns, that can be adjusted too.

 

[Eric W]

It also occurs to me that the #N/A values are the results of formulas. If so, you can wrap those formulas in the IFERROR function, and then you just need a regular =SUM(C15,C21,C27,C28,C34,C40,C46,C52,C58) formula.

 

[Peter_SSs]

I've since created a formula that works.. but it's not pretty.

Is there a way to simplify it / stream line it into a SUMIFS? This is a real example of the formula I've not got working and I'm able to drag it across the row with the totals.

=SUMIF(C15,"<>#N/A")+SUMIF(C21,"<>#N/A")+SUMIF(C27,"<>#N/A")+SUMIF(C28,"<>#N/A")+SUMIF(C34,"<>#N/A")+SUMIF(C40,"<>#N/A")+SUMIF(C46,"<>#N/A")+SUMIF(C52,"<>#N/A")+SUMIF(C58,"<>#N/A")

Yes, try this copied across

=AGGREGATE(9,6,C15,C21,C27:C28,C34,C40,C46,C52,C58)


(Though I agree with Eric that it would be much tidier & easier if you altered the formulas (assuming they are formulas) producing the #N/A errors)

 

[MixedUpExcel]

Thank You Eric,

Your formula does work.

I have to have the #N/A's - these are by design as the results where actually 0 (that would have been easier wouldn't it! ) but as the data feeds a line chart, I don't want the line to drop to the 0 line for the months which haven't happened yet.

Thanks again.

 

[MixedUpExcel]

Thanks Peter,

Yours also does what I need.. never heard of aggregate before but have just found a site which details the function and the need for 9,2 at the start of the formula.

Thanks again.

Simon