counting a number of cells with 2 range criteria

[Garf]

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

 

[Roger Govier]

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 A1>Data>Pivot Table>Finish
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 Data>List>Create>my data had Headers, before
carrying out the steps above.

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

After entering new data, right click on the PT report>Refresh

 

[Bernard Liengme]

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

 

[Garf]

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

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 A1>Data>Pivot Table>Finish
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 Data>List>Create>my data had Headers, before
carrying out the steps above.

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

After entering new data, right click on the PT report>Refresh