Calculate sum of certain cells ignoring #n/a

N

NicoleV

New Member
Joined
May 17, 2017
Messages
3
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
[TABLE="width: 1018"]
<tbody>[TR]
[TD]Model Code[/TD]
[TD]Model Description[/TD]
[TD]EBQ Code[/TD]
[TD]EBQ Description[/TD]
[TD]QTY[/TD]
[TD]Export[/TD]
[TD]Total[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]LIGHT FITTINGS:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PS0050[/TD]
[TD]Light - T5 Recessed Fixture 2x28W[/TD]
[TD][/TD]
[TD]1200 x 600 LBR -RCO(2*54W) Recessed - white[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]PS0050[/TD]
[TD]Light - T5 Recessed Fixture 2x28W[/TD]
[TD][/TD]
[TD]T5 54W Flourescent lamps 840 (including 4 spare)[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PS0056[/TD]
[TD]Light - Shopfront 3m Track with 3 Spotlights[/TD]
[TD]EBQ000183[/TD]
[TD]Shopfront window display (21W LED track spots)[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]PS0056[/TD]
[TD]Light - Shopfront 3m Track with 3 Spotlights[/TD]
[TD]EBQ000184[/TD]
[TD]Shopfront window display - track (3m track)[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]PS0056[/TD]
[TD]Light - Shopfront 3m Track with 3 Spotlights[/TD]
[TD]EBQ000091[/TD]
[TD]Light downlight holder - black[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PS0057[/TD]
[TD]Light - Perimeter wall washer[/TD]
[TD][/TD]
[TD]T5 54W Flourescent lamps 840 (including 4 spare)[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]PS0057[/TD]
[TD]Light - Perimeter wall washer[/TD]
[TD][/TD]
[TD]Perimeter wall washer -Raw 254(1200*200) - white[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PS0058[/TD]
[TD]Light - Shopfront 3m Track with 4 Spotlights[/TD]
[TD]EBQ000183[/TD]
[TD]Shopfront window display (21W LED track spots)[/TD]
[TD="align: right"]4[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]PS0058[/TD]
[TD]Light - Shopfront 3m Track with 4 Spotlights[/TD]
[TD]EBQ000184[/TD]
[TD]Shopfront window display - track (3m track)[/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]PS0058[/TD]
[TD]Light - Shopfront 3m Track with 4 Spotlights[/TD]
[TD]EBQ000091[/TD]
[TD]Light downlight holder - black[/TD]
[TD="align: right"]4[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PS0059[/TD]
[TD]Light - Shopfront 3m Track with 5 Spotlights[/TD]
[TD]EBQ000183[/TD]
[TD]Shopfront window display (21W LED track spots)[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]PS0059[/TD]
[TD]Light - Shopfront 3m Track with 5 Spotlights[/TD]
[TD]EBQ000184[/TD]
[TD]Shopfront window display - track (3m track)[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]PS0059[/TD]
[TD]Light - Shopfront 3m Track with 5 Spotlights[/TD]
[TD]EBQ000091[/TD]
[TD]Light downlight holder - black[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




SHEET 2: SUMMARY SHEET
[TABLE="width: 614"]
<tbody>[TR]
[TD]Item Code[/TD]
[TD]Item Description[/TD]
[TD]Price[/TD]
[TD]QTY[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD="colspan: 5"]LIGHT FITTINGS:[/TD]
[/TR]
[TR]
[TD]EBQ000091[/TD]
[TD]Light downlight holder - white[/TD]
[TD="align: right"]28.86[/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]EBQ000091[/TD]
[TD]Light downlight holder - black[/TD]
[TD="align: right"]28.86[/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]EBQ000101[/TD]
[TD]T5 Lighting bracket for wall washer[/TD]
[TD="align: right"]16.24[/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]EBQ000183[/TD]
[TD]Shopfront window display (21W LED track spots)[/TD]
[TD="align: right"]864.92[/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]EBQ000184[/TD]
[TD]Shopfront window display - track (3m track)[/TD]
[TD="align: right"]345.98[/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[/TR]
</tbody>[/TABLE]



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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
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.
 
Last edited:
Upvote 0
NicoleV said:
=SUMIF(range,"<>N/A") doesn't work because its not looking for the total of one EBQ code
Click to expand...

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

But perhaps you want =SUMIFS(range1, range2, "EBQ000091", range3, "<>#N/A")
 
Upvote 0
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
 
Upvote 0
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[TD]
D​
[/TD]
[TD]
E​
[/TD]
[TD]
F​
[/TD]
[TD]
G​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD]HEET 1: DATA SHEET[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]Model Code[/TD]
[TD]Model Description[/TD]
[TD]EBQ Code[/TD]
[TD]EBQ Description[/TD]
[TD]QTY[/TD]
[TD]Export[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]LIGHT FITTINGS:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]PS0050[/TD]
[TD]Light - T5 Recessed Fixture 2x28W[/TD]
[TD][/TD]
[TD]1200 x 600 LBR -RCO(2*54W) Recessed - white[/TD]
[TD]
1​
[/TD]
[TD]
23​
[/TD]
[TD]
23​
[/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD]PS0050[/TD]
[TD]Light - T5 Recessed Fixture 2x28W[/TD]
[TD][/TD]
[TD]T5 54W Flourescent lamps 840 (including 4 spare)[/TD]
[TD]
2​
[/TD]
[TD]
23​
[/TD]
[TD]
46​
[/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD]PS0056[/TD]
[TD]Light - Shopfront 3m Track with 3 Spotlights[/TD]
[TD]EBQ000183[/TD]
[TD]Shopfront window display (21W LED track spots)[/TD]
[TD]
3​
[/TD]
[TD]
6​
[/TD]
[TD]
18​
[/TD]
[/TR]
[TR]
[TD]
7​
[/TD]
[TD]PS0056[/TD]
[TD]Light - Shopfront 3m Track with 3 Spotlights[/TD]
[TD]EBQ000184[/TD]
[TD]Shopfront window display - track (3m track)[/TD]
[TD]
1​
[/TD]
[TD]
6​
[/TD]
[TD]
6​
[/TD]
[/TR]
[TR]
[TD]
8​
[/TD]
[TD]PS0056[/TD]
[TD]Light - Shopfront 3m Track with 3 Spotlights[/TD]
[TD]EBQ000091[/TD]
[TD]Light downlight holder - black[/TD]
[TD]
3​
[/TD]
[TD]
6​
[/TD]
[TD]
18​
[/TD]
[/TR]
[TR]
[TD]
9​
[/TD]
[TD]PS0057[/TD]
[TD]Light - Perimeter wall washer[/TD]
[TD][/TD]
[TD]T5 54W Flourescent lamps 840 (including 4 spare)[/TD]
[TD]
2​
[/TD]
[TD]
9​
[/TD]
[TD]
18​
[/TD]
[/TR]
[TR]
[TD]
10​
[/TD]
[TD]PS0057[/TD]
[TD]Light - Perimeter wall washer[/TD]
[TD][/TD]
[TD]Perimeter wall washer -Raw 254(1200*200) - white[/TD]
[TD]
1​
[/TD]
[TD]
9​
[/TD]
[TD]
9​
[/TD]
[/TR]
[TR]
[TD]
11​
[/TD]
[TD]PS0058[/TD]
[TD]Light - Shopfront 3m Track with 4 Spotlights[/TD]
[TD]EBQ000183[/TD]
[TD]Shopfront window display (21W LED track spots)[/TD]
[TD]
4​
[/TD]
[TD]
#N/A​
[/TD]
[TD]
#N/A​
[/TD]
[/TR]
[TR]
[TD]
12​
[/TD]
[TD]PS0058[/TD]
[TD]Light - Shopfront 3m Track with 4 Spotlights[/TD]
[TD]EBQ000184[/TD]
[TD]Shopfront window display - track (3m track)[/TD]
[TD]
1​
[/TD]
[TD]
#N/A​
[/TD]
[TD]
#N/A​
[/TD]
[/TR]
[TR]
[TD]
13​
[/TD]
[TD]PS0058[/TD]
[TD]Light - Shopfront 3m Track with 4 Spotlights[/TD]
[TD]EBQ000091[/TD]
[TD]Light downlight holder - black[/TD]
[TD]
4​
[/TD]
[TD]
#N/A​
[/TD]
[TD]
#N/A​
[/TD]
[/TR]
[TR]
[TD]
14​
[/TD]
[TD]PS0059[/TD]
[TD]Light - Shopfront 3m Track with 5 Spotlights[/TD]
[TD]EBQ000183[/TD]
[TD]Shopfront window display (21W LED track spots)[/TD]
[TD]
5​
[/TD]
[TD]
1​
[/TD]
[TD]
5​
[/TD]
[/TR]
[TR]
[TD]
15​
[/TD]
[TD]PS0059[/TD]
[TD]Light - Shopfront 3m Track with 5 Spotlights[/TD]
[TD]EBQ000184[/TD]
[TD]Shopfront window display - track (3m track)[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD]
16​
[/TD]
[TD]PS0059[/TD]
[TD]Light - Shopfront 3m Track with 5 Spotlights[/TD]
[TD]EBQ000091[/TD]
[TD]Light downlight holder - black[/TD]
[TD]
5​
[/TD]
[TD]
1​
[/TD]
[TD]
5​
[/TD]
[/TR]
</tbody>[/TABLE]


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


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
 
Upvote 0
Thanks for your help guys.
I figured it out after a brain-wrecking 2 days.
Here's what I did:



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

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

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


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
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,711
Messages
6,174,025
Members
452,542
Latest member
Bricklin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top