I have 3 ranges

1. week number
2. Rep code
3. Product value

how do i count the number of weeks that pertain to a specific rep code i.e
countif rep code is equal to 101 and week number is wk 1

Also how do i sum the product value if the weeks relate to say week 1 and
rep code is equal to 101

1 £450 104
1 £1,000 106
1 £1,000 102
1 £800 106
1 £500 106
2 £2,000 102
2 £2,200 104
2 £1,230 104
2 £1,000 102
2 £1,000 108

I want to link the details in to a weekly tracker for sales reps but the
values might change so I can't use filters

Thanks

Garf

Hi

for the Count
=SUMPRODUCT(--($A:1:$A$100)=1),--($C$1:$C$100=101))
For the Value
=SUMPRODUCT(--($A:1:$A$100)=1),--($C$1:$C$100=101),$B$1:$B$100)

But, why not use a Pivot Table instead?
Insert a header row at row 1 with Week, Value and Rep in A1:C1
Place cursor in A1DataPivot TableFinish
On the PT skeleton that appears on a new sheet,
Drag Rep to the Row area
Drag Week to the Column Area
Drag Value to the Data area as Sum of Value

If you prefer, drag Week to the page area, then use the dropdown to select
just one individual week.

The PT that will have been created will be based upon the range of data on
your sheet. As this data will grow as you add more information each week,
you need to have a dynamic range.
If you have XL2003, Use DataListCreatemy data had Headers, before
carrying out the steps above.

If you have an earlier version of Excel, then InsertNameDefine
Name myData
Refers to =$A$1:INDEX($C$C,COUNTA($A:$A))
Right click on any cell in your PTPT WizardbackSource =myData in place
of the fixed range that will have been created.

After entering new data, right click on the PT reportRefresh

--
Regards
Roger Govier

"Garf" wrote in message
...
I have 3 ranges

1. week number
2. Rep code
3. Product value

how do i count the number of weeks that pertain to a specific rep code i.e
countif rep code is equal to 101 and week number is wk 1

Also how do i sum the product value if the weeks relate to say week 1 and
rep code is equal to 101

1 £450 104
1 £1,000 106
1 £1,000 102
1 £800 106
1 £500 106
2 £2,000 102
2 £2,200 104
2 £1,230 104
2 £1,000 102
2 £1,000 108

I want to link the details in to a weekly tracker for sales reps but the
values might change so I can't use filters

Thanks

Garf

First your description does not match the sample data: is RepCode in column
B or C?
I will assume C as in the sample data
Q1) Do you want, for example, the answer for Rep 106 to be
either 3 -- that code appears three times
=COUNTIF(C1:C100,106)

or 1 -- all three are for the same week
I will look at this if that is what you need - please let us know

Q2: =SUMPRODUCT(--(A1:A100=1),--(C1:C100=101),B1:B100)
Only in Excel 2007 can you use full column references
=SUMPRODUCT(--(A:A=1),--(C:C=101),B:B)
Alternatively, in Excel 2007 you could use COUNTIFS (note the final S)

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Garf" wrote in message
...
I have 3 ranges

1. week number
2. Rep code
3. Product value

how do i count the number of weeks that pertain to a specific rep code i.e
countif rep code is equal to 101 and week number is wk 1

Also how do i sum the product value if the weeks relate to say week 1 and
rep code is equal to 101

1 £450 104
1 £1,000 106
1 £1,000 102
1 £800 106
1 £500 106
2 £2,000 102
2 £2,200 104
2 £1,230 104
2 £1,000 102
2 £1,000 108

I want to link the details in to a weekly tracker for sales reps but the
values might change so I can't use filters

Thanks

Garf

Thanks Roger that's great, will give it a whirl

"Roger Govier" wrote:

Hi

for the Count
=SUMPRODUCT(--($A:1:$A$100)=1),--($C$1:$C$100=101))
For the Value
=SUMPRODUCT(--($A:1:$A$100)=1),--($C$1:$C$100=101),$B$1:$B$100)

But, why not use a Pivot Table instead?
Insert a header row at row 1 with Week, Value and Rep in A1:C1
Place cursor in A1DataPivot TableFinish
On the PT skeleton that appears on a new sheet,
Drag Rep to the Row area
Drag Week to the Column Area
Drag Value to the Data area as Sum of Value

If you prefer, drag Week to the page area, then use the dropdown to select
just one individual week.

The PT that will have been created will be based upon the range of data on
your sheet. As this data will grow as you add more information each week,
you need to have a dynamic range.
If you have XL2003, Use DataListCreatemy data had Headers, before
carrying out the steps above.

If you have an earlier version of Excel, then InsertNameDefine
Name myData
Refers to =$A$1:INDEX($C$C,COUNTA($A:$A))
Right click on any cell in your PTPT WizardbackSource =myData in place
of the fixed range that will have been created.

After entering new data, right click on the PT reportRefresh

--
Regards
Roger Govier

"Garf" wrote in message
...
I have 3 ranges

1. week number
2. Rep code
3. Product value

how do i count the number of weeks that pertain to a specific rep code i.e
countif rep code is equal to 101 and week number is wk 1

Also how do i sum the product value if the weeks relate to say week 1 and
rep code is equal to 101

1 £450 104
1 £1,000 106
1 £1,000 102
1 £800 106
1 £500 106
2 £2,000 102
2 £2,200 104
2 £1,230 104
2 £1,000 102
2 £1,000 108

I want to link the details in to a weekly tracker for sales reps but the
values might change so I can't use filters

Thanks

Garf