Multiple Criteria SUMIF (or SUMPRODUCT)

[rajmistry]

Hello

I have a problem which I cannot resolve. I've tried various sumif's, sumproducts, etc but cannot get thr right formula.

date = column H
Category = column E
Cost = column x

Basically what i'm trying to do is;
if Category = PDMve, then sum the total cost for that week

Can anyone help please?

 

[Matty]

Welcome to the Board!

Could you share a small example of how your data's laid out?

Easiest way to do this is to ensure there are black borders around your cells, select those cells, hit copy and then paste directly into your message.

Matty

 

[rajmistry]

here is the example.

<TABLE style="WIDTH: 797pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1061><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 2011" width=55><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2267" width=62><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1682" width=46><COL style="WIDTH: 99pt; mso-width-source: userset; mso-width-alt: 4827" span=2 width=132><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><COL style="WIDTH: 99pt; mso-width-source: userset; mso-width-alt: 4827" span=3 width=132><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><COL style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1938" width=53><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #bfbfbf; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87 height=20 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #bfbfbf; WIDTH: 41pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87 width=55>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #bfbfbf; WIDTH: 47pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87 width=62>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #bfbfbf; WIDTH: 35pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87 width=46>C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #bfbfbf; WIDTH: 99pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87 width=132>D</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #bfbfbf; WIDTH: 99pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87 width=132>E</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #bfbfbf; WIDTH: 42pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87 width=56>F</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #bfbfbf; WIDTH: 99pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87 width=132>H</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #bfbfbf; WIDTH: 99pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87 width=132>J</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #bfbfbf; WIDTH: 99pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87 width=132>K</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #bfbfbf; WIDTH: 49pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87 width=65>X</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #bfbfbf; WIDTH: 40pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87 width=53>Y</TD></TR><TR style="HEIGHT: 36pt; mso-height-source: userset" height=48><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #bfbfbf; HEIGHT: 36pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87 height=48>1</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #fcd5b4; WIDTH: 41pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl85 width=55>ID Code</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fcd5b4; WIDTH: 47pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=62>From</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; WIDTH: 35pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 width=46>To</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; WIDTH: 99pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 width=132>Cat</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; WIDTH: 99pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl86 width=132>Sub Cat</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; WIDTH: 42pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 width=56>For</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; WIDTH: 99pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl86 width=132>Collection
Date</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; WIDTH: 99pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 width=132>Freight
Forwarder</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; WIDTH: 99pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 width=132>Part Number/s</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; WIDTH: 49pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl86 width=65>Total Cost</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 40pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=53>VAT</TD></TR><TR style="HEIGHT: 30pt" height=40><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #bfbfbf; HEIGHT: 30pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87 height=40>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69 width=55>ST001</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69 width=62>Loc1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 35pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 width=46>Loc2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 width=132>Prod</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; WIDTH: 99pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl76 width=132>PDMve</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 width=56>JA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; WIDTH: 99pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl79 width=132>08-Jun</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 width=132>FF1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 width=132>5XXXX</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; WIDTH: 49pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl83 width=65>£600.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 40pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 width=53>£120.00</TD></TR><TR style="HEIGHT: 30pt" height=40><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #bfbfbf; HEIGHT: 30pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87 height=40>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl73 width=55>ST002</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl73 width=62>Loc1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 35pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl74 width=46>Loc2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl74 width=132>Prod</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl77 width=132>PDMve</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl74 width=56>PS</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl80 width=132>04-Jul</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl74 width=132>FF1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl74 width=132>5XXXX</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; WIDTH: 49pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl82 width=65>£1,000.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 40pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 width=53>£200.00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #bfbfbf; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87 height=20>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl73 width=55>ST003</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl73 width=62>Loc3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 35pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl74 width=46>Loc1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl74 width=132>euro</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl77 width=132>Euro1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl74 width=56>RM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl80 width=132>01-Jul</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl74 width=132>FF2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl74 width=132>5XXXX</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; WIDTH: 49pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl82 width=65>£1,200.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 40pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 width=53>£240.00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #bfbfbf; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87 height=20>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl73 width=55>ST004</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl73 width=62>Loc4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 35pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl74 width=46>Loc2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl74 width=132>Prod</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl77 width=132>PDPrt</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl74 width=56>RM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl80 width=132>01-Sep</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl74 width=132>FF3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl74 width=132>5XXXX</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; WIDTH: 49pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl82 width=65>£800.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 40pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 width=53>£160.00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #bfbfbf; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87 height=20>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl73 width=55>ST005</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl73 width=62>Loc1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 35pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl74 width=46>Loc7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl74 width=132>euro</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl77 width=132>Euro2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl74 width=56>AB</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl80 width=132>09-Sep</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl74 width=132>FF5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl74 width=132>5XXXX</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; WIDTH: 49pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl82 width=65>£900.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 40pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 width=53>£180.00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #bfbfbf; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87 height=20>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl73 width=55>ST006</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl73 width=62>Loc1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 35pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl74 width=46>Loc2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl74 width=132>Prod</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl77 width=132>PDMve</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl74 width=56>AB</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl80 width=132>23-Sep</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl74 width=132>FF1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl74 width=132>5XXXX</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; WIDTH: 49pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl82 width=65>£800.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 40pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 width=53>£160.00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #bfbfbf; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87 height=20>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl73 width=55>ST007</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl73 width=62>Loc5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 35pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl74 width=46>Loc2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl74 width=132>Prod</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl77 width=132>PDMve</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl74 width=56>RM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl80 width=132>29-Sep</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl74 width=132>FF4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl74 width=132>5XXXX</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; WIDTH: 49pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl82 width=65>£600.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 40pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 width=53>£120.00</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #bfbfbf; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87 height=21>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl73 width=55>ST008</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl73 width=62>Loc6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 35pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl74 width=46>loc1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl74 width=132>other</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl78 width=132>Other</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl74 width=56>OD</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl81 width=132>03-Oct</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl74 width=132>FF1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl74 width=132>OTHER</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; WIDTH: 49pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl84 width=65>£50.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 40pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 width=53>£10.00</TD></TR></TBODY></TABLE>


Hightlighted areas is ths data i want to choose from.
the date part of it is when the transport was arranged, but in the formula i want it so it looks at a date range (a week)

 

[rajmistry]

please please please can anyone help?

 

[Jonmo1]

Welcome to the board..

You'll need to define your week...

Example, Sunday (10/02/2011) through Saturday (10/08/2011)

Try

=SUMPRODUCT(--(E2:E10="PDMve"),--(H2:H10>=DATE(2011,10,2)),--(H2:H10<=DATE(2011,10,8)),X2:X10)

Hope that helps.

 

[rajmistry]

Thanks jonmo1

trying it now.
what are the double dashes for?

 

[Jonmo1]

Thanks jonmo1

trying it now.
what are the double dashes for?

Glad to help..

The -- convert a True/False result into 1/0
True = 1
False = 0

 

[rajmistry]

Works perfectly.
Thank you very much