Calculate sum of certain cells ignoring #n/a

[NicoleV]

I have 2 sheets.
The first sheet has the data and the second is basically a summary sheet.
Here's what I need to do:

SHEET 1: DATA SHEET

Model Code	Model Description	EBQ Code	EBQ Description	QTY	Export	Total
LIGHT FITTINGS:
PS0050	Light - T5 Recessed Fixture 2x28W		1200 x 600 LBR -RCO(2*54W) Recessed - white	1	23	23
PS0050	Light - T5 Recessed Fixture 2x28W		T5 54W Flourescent lamps 840 (including 4 spare)	2	23	46
PS0056	Light - Shopfront 3m Track with 3 Spotlights	EBQ000183	Shopfront window display (21W LED track spots)	3	6	18
PS0056	Light - Shopfront 3m Track with 3 Spotlights	EBQ000184	Shopfront window display - track (3m track)	1	6	6
PS0056	Light - Shopfront 3m Track with 3 Spotlights	EBQ000091	Light downlight holder - black	3	6	18
PS0057	Light - Perimeter wall washer		T5 54W Flourescent lamps 840 (including 4 spare)	2	9	18
PS0057	Light - Perimeter wall washer		Perimeter wall washer -Raw 254(1200*200) - white	1	9	9
PS0058	Light - Shopfront 3m Track with 4 Spotlights	EBQ000183	Shopfront window display (21W LED track spots)	4	#N/A	#N/A
PS0058	Light - Shopfront 3m Track with 4 Spotlights	EBQ000184	Shopfront window display - track (3m track)	1	#N/A	#N/A
PS0058	Light - Shopfront 3m Track with 4 Spotlights	EBQ000091	Light downlight holder - black	4	#N/A	#N/A
PS0059	Light - Shopfront 3m Track with 5 Spotlights	EBQ000183	Shopfront window display (21W LED track spots)	5	1	5
PS0059	Light - Shopfront 3m Track with 5 Spotlights	EBQ000184	Shopfront window display - track (3m track)	1	1	1
PS0059	Light - Shopfront 3m Track with 5 Spotlights	EBQ000091	Light downlight holder - black	5	1	5

<tbody>
</tbody>

SHEET 2: SUMMARY SHEET

Item Code	Item Description	Price	QTY	Total
LIGHT FITTINGS:
EBQ000091	Light downlight holder - white	28.86		0.00
EBQ000091	Light downlight holder - black	28.86		0.00
EBQ000101	T5 Lighting bracket for wall washer	16.24		0.00
EBQ000183	Shopfront window display (21W LED track spots)	864.92		0.00
EBQ000184	Shopfront window display - track (3m track)	345.98		0.00

<tbody>
</tbody>

So what I want to do is to create a formula in (SHEET 2) QTY column that will tell me the total amount of "EBQ000091 Light downlight holder - black" I need to order in total (Total column in SHEET 1)

=SUMIF(range,"<>N/A") doesn't work because its not looking for the total of one EBQ code

 

[RoryA]

Welcome to the forum.

It sounds like you just want SUMIFS rather than SUMIF, but I can't see where the #N/A comes into it?

Edit: Never mind, I couldn't see the full table on my Mac for some reason.

 

[joeu2004]

=SUMIF(range,"<>N/A") doesn't work because its not looking for the total of one EBQ code

That should be =SUMIF(range, "<>#N/A").

But perhaps you want =SUMIFS(range1, range2, "EBQ000091", range3, "<>#N/A")

 

[NicoleV]

I'm struggling to add screenshots of my sheets which would help to explain better.
Will respond as soon as I figure out how to do this image adding thing

 

[MARZIOTULLIO]

	A​	B​	C​	D​	E​	F​	G​
1​	HEET 1: DATA SHEET
2​	Model Code	Model Description	EBQ Code	EBQ Description	QTY	Export	Total
3​	LIGHT FITTINGS:
4​	PS0050	Light - T5 Recessed Fixture 2x28W		1200 x 600 LBR -RCO(2*54W) Recessed - white	1​	23​	23​
5​	PS0050	Light - T5 Recessed Fixture 2x28W		T5 54W Flourescent lamps 840 (including 4 spare)	2​	23​	46​
6​	PS0056	Light - Shopfront 3m Track with 3 Spotlights	EBQ000183	Shopfront window display (21W LED track spots)	3​	6​	18​
7​	PS0056	Light - Shopfront 3m Track with 3 Spotlights	EBQ000184	Shopfront window display - track (3m track)	1​	6​	6​
8​	PS0056	Light - Shopfront 3m Track with 3 Spotlights	EBQ000091	Light downlight holder - black	3​	6​	18​
9​	PS0057	Light - Perimeter wall washer		T5 54W Flourescent lamps 840 (including 4 spare)	2​	9​	18​
10​	PS0057	Light - Perimeter wall washer		Perimeter wall washer -Raw 254(1200*200) - white	1​	9​	9​
11​	PS0058	Light - Shopfront 3m Track with 4 Spotlights	EBQ000183	Shopfront window display (21W LED track spots)	4​	#N/A​	#N/A​
12​	PS0058	Light - Shopfront 3m Track with 4 Spotlights	EBQ000184	Shopfront window display - track (3m track)	1​	#N/A​	#N/A​
13​	PS0058	Light - Shopfront 3m Track with 4 Spotlights	EBQ000091	Light downlight holder - black	4​	#N/A​	#N/A​
14​	PS0059	Light - Shopfront 3m Track with 5 Spotlights	EBQ000183	Shopfront window display (21W LED track spots)	5​	1​	5​
15​	PS0059	Light - Shopfront 3m Track with 5 Spotlights	EBQ000184	Shopfront window display - track (3m track)	1​	1​	1​
16​	PS0059	Light - Shopfront 3m Track with 5 Spotlights	EBQ000091	Light downlight holder - black	5​	1​	5​

<tbody>
</tbody>

	A​	B​	C​	D​	E​
1​	SHEET 2: SUMMARY SHEET
2​	Item Code	Item Description	Price	QTY	Total
3​	LIGHT FITTINGS:
4​	EBQ000091	Light downlight holder - white	28.86​	0​	0​
5​	EBQ000091	Light downlight holder - black	28.86​	12​	23​
6​	EBQ000101	T5 Lighting bracket for wall washer	16.24​	0​	0​
7​	EBQ000183	Shopfront window display (21W LED track spots)	864.92​	12​	23​
8​	EBQ000184	Shopfront window display - track (3m track)	345.98​	3​	7​

<tbody>
</tbody>

SHEET2

D4=SUMIFS(Sheet1!$E$4:$E$16,Sheet1!$C$4:$C$16,Sheet2!$A4,Sheet1!$D$4:$D$16,Sheet2!$B4) copy down

E4=SUMIFS(Sheet1!$G$4:$G$16,Sheet1!$G$4:$G$16,"<>#N/A",Sheet1!$C$4:$C$16,Sheet2!$A4,Sheet1!$D$4:$D$16,Sheet2!$B4) copy down

 

[NicoleV]

Thanks for your help guys.
I figured it out after a brain-wrecking 2 days.
Here's what I did:

A​	B​	C​	D​	E​	F​	G​
1​	HEET 1: DATA SHEET
2​	Model Code	Model Description	EBQ Code	EBQ Description	QTY	Export	Total
3​	LIGHT FITTINGS:
4​	PS0050	Light - T5 Recessed Fixture 2x28W		1200 x 600 LBR -RCO(2*54W) Recessed - white	1​	23​	23​
5​	PS0050	Light - T5 Recessed Fixture 2x28W		T5 54W Flourescent lamps 840 (including 4 spare)	2​	23​	46​
6​	PS0056	Light - Shopfront 3m Track with 3 Spotlights	EBQ000183	Shopfront window display (21W LED track spots)	3​	6​	18​
7​	PS0056	Light - Shopfront 3m Track with 3 Spotlights	EBQ000184	Shopfront window display - track (3m track)	1​	6​	6​
8​	PS0056	Light - Shopfront 3m Track with 3 Spotlights	EBQ000091	Light downlight holder - black	3​	6​	18​
9​	PS0057	Light - Perimeter wall washer		T5 54W Flourescent lamps 840 (including 4 spare)	2​	9​	18​
10​	PS0057	Light - Perimeter wall washer		Perimeter wall washer -Raw 254(1200*200) - white	1​	9​	9​
11​	PS0058	Light - Shopfront 3m Track with 4 Spotlights	EBQ000183	Shopfront window display (21W LED track spots)	4​	#N/A​	0​
12​	PS0058	Light - Shopfront 3m Track with 4 Spotlights	EBQ000184	Shopfront window display - track (3m track)	1​	#N/A​	0​
13​	PS0058	Light - Shopfront 3m Track with 4 Spotlights	EBQ000091	Light downlight holder - black	4​	#N/A​	0​
14​	PS0059	Light - Shopfront 3m Track with 5 Spotlights	EBQ000183	Shopfront window display (21W LED track spots)	5​	1​	5​
15​	PS0059	Light - Shopfront 3m Track with 5 Spotlights	EBQ000184	Shopfront window display - track (3m track)	1​	1​	1​
16​	PS0059	Light - Shopfront 3m Track with 5 Spotlights	EBQ000091	Light downlight holder - black	5​	1​	5​

<tbody>
</tbody>

I CHANGED THE FORMULA IN THE TOTAL COLUMN TO ELIMINATE ALL N/A RESULTS:
=IFERROR(F13*G13,0)

	A​	B​	C​	D​	E​
1​	SHEET 2: SUMMARY SHEET
2​	Item Code	Item Description	Price	QTY	Total
3​	LIGHT FITTINGS:
4​	EBQ000091	Light downlight holder - white	28.86​	0​	0​
5​	EBQ000091	Light downlight holder - black	28.86​	12​	23​
6​	EBQ000101	T5 Lighting bracket for wall washer	16.24​	0​	0​
7​	EBQ000183	Shopfront window display (21W LED track spots)	864.92​	12​	23​
8​	EBQ000184	Shopfront window display - track (3m track)	345.98​	3​	7​

<tbody>
</tbody>

I THEN USED THE SUMIF FUNCTION IN D1 TO GIVE ME THE CORRECT INFORMATION THAT I WAS LOOKING FOR.

THANK YOU ONCE AGAIN FOR YOUR HELP GUYS