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:

2017

2018

JAN

FEB

MAR

APR

MAY

JUN

JUL

AUG

SEPT

OCT

NOV

DEC

JAN

FEB

MAR

APR

MAY

JUN

JUL

AUG

SEP

OCT

NOV

Pack 1

250

Component 1

VALUE

Component 2

VALUE

Pack 2

175

Component 1

VALUE

Component 2

VALUE

Component 3

VALUE

Pack

93

Component 1

VALUE

Component 2

VALUE

Component 3

VALUE

Component 4

VALUE

Component 5

VALUE

Pack

#N/A

Component 1

#N/A

Component 2

#N/A

Component 3

#N/A

Pack

#N/A

Component 1

#N/A

Component 2

#N/A

Component 3

#N/A

Component 4

#N/A

Pack

374

Component 1

VALUE

Component 2

VALUE

Component 3

VALUE

Component 4

VALUE

Pack

187

Component 1

VALUE

Component 2

VALUE

Component 3

VALUE

Pack

#N/A

Component 1

#N/A

Component 2

#N/A

Component 3

#N/A

Component 4

#N/A

Component 5

#N/A

PACK TOTAL

1079

<tbody>
</tbody>

Thanks in advance.

Simon

 

[cunningAce]

This should do it,

=SUMIFS($C$69:$C$112,$A$69:$A$112,"Pack*",$B$69:$B$112,"<>#N/A")

 

[AlanY]

try

=SUM(IF(ISERROR(C69:C112),"",C69:C112))

confirm with shift-control-enter together

 

[MixedUpExcel]

This should do it,

=SUMIFS($C$69:$C$112,$A$69:$A$112,"Pack*",$B$69:$B$112,"<>#N/A")

Thanks cunningAce, unfortunately, I only used Pack to illustrate that I wanted to SUM the values against the PACK - the Packs actually have individual names.

So I'm not going to be able to use this formula.

Thanks anyway.

S.

 

[MixedUpExcel]

Hi,

I feel I had better make a few more notes on my above table.

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.

Hope that clears things up a little bit.

AlanY,

I've tried your formula and as per my update above, this won't work.

Thanks anyway for throwing in a suggestion.

Simon

 

[AlanY]

it works for me

Excel 2013/2016
	A	B	C	D
1		2017
2		JAN	FEB	MAR
3	Pack 1	250
4	Component 1	VALUE
5	Component 2	VALUE
6	Pack 2	175
7	Component 1	VALUE
8	Component 2	VALUE
9	Component 3	VALUE
10	Pack	93
11	Component 1	VALUE
12	Component 2	VALUE
13	Component 3	VALUE
14	Component 4	VALUE
15	Component 5	VALUE
16	Pack	#N/A
17	Component 1	#N/A
18	Component 2	#N/A
19	Component 3	#N/A
20	Pack	#N/A
21	Component 1	#N/A
22	Component 2	#N/A
23	Component 3	#N/A
24	Component 4	#N/A
25	Pack	374
26	Component 1	VALUE
27	Component 2	VALUE
28	Component 3	VALUE
29	Component 4	VALUE
30	Pack	187
31	Component 1	VALUE
32	Component 2	VALUE
33	Component 3	VALUE
34	Pack	#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	PACK TOTAL	1079
41
42		1079
Sheet2

Cell Formulas
Range	Formula
B42	{=SUM(IF(ISERROR(B3:B39),"",B3:B39))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[cunningAce]

Cant you add a column to illustrate whether the row contains 'Pack' data or 'Component Data. That way the formula could be used?

 

[MixedUpExcel]

Hi AlanY,

You're formula does work but only if there is no actual numbers where I have put Value.

As per a more recent post where I realised that I hadn't been clear.. I put this:

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.

If I replace any of the VALUE's in my table to a number (which is what I have on my spreadsheet) then those numbers also get added into the Total - which I don't want.

I need to have a formula where I can select the cells under each month (one column at a time) next to the Pack Name (not the Component) and then get a Total, irrespective of whether some or none have the #N/A.

I've replaced Value in the table below but still have the same numbers next to the packs and I still want to be able to get to 1079

A	B	C	D
1		2017
2		JAN	FEB	MAR
3	Pack 1	250
4	Component 1	130
5	Component 2	120
6	Pack 2	175
7	Component 1	75
8	Component 2	60
9	Component 3	40
10	Pack	93
11	Component 1	63
12	Component 2	10
13	Component 3	10
14	Component 4	6
15	Component 5	4
16	Pack	#N/A
17	Component 1	#N/A
18	Component 2	#N/A
19	Component 3	#N/A
20	Pack	#N/A
21	Component 1	#N/A
22	Component 2	#N/A
23	Component 3	#N/A
24	Component 4	#N/A
25	Pack	374
26	Component 1	150
27	Component 2	120
28	Component 3	50
29	Component 4	54
30	Pack	187
31	Component 1	100
32	Component 2	47
33	Component 3	40
34	Pack	#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	PACK TOTAL	1079
41
42		1079

<tbody>
</tbody><thead>
</thead><tbody>
</tbody>

Thanks.

 

[MixedUpExcel]

Hi cunningAce,

Please see my last post to AlanY.

Hope that might clear it up - I'm just working one column at a time, then I hope to be able to drag the formula over which is used to generate the Total across all other months.

I just want to Total those which are in Bold which are against the Packs but the name Pack will change in my real life situation - that is just to indicate that that Row is what I need to SUM.

Thanks.

 

[AlanY]

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

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

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