COUNT IF
 

I have a table with Department, Practice and various amounts

For each department, practice combination I want to count the number of
amounts between 500-1000, 1001-1500, 1501-2000, etc.

I tried FREQUENCY, but found it fine when all I had to do was count for one
departments, but now that I need department/practice combinations, it seems
to be too cumbersome.

Thanks in advance.

Sample of data where column 1 is department, column 3 is practice and column
2 is amount

01
$3,000.00
17

01
$818.90
17

01
$1,098.20
17

01
$500.00
999

01
$500.00
999

01
$500.00
999

01
$540.00
999

01
$593.80
999

01
$628.52
999

01
$797.34
999

01
$797.34
999

01
$797.34
999

01
$1,157.35
999

01
$750.00
999

01
$1,343.25
999

01
$1,000.00
999

01
$3,355.80
999

01
$616.50
999

02
$540.00
4

02
$516.95
4

02
$538.89
4

02
$1,240.32
5

COUNT IF
 

SUMPRODUCT is used for multiple criteria.
If you set up your report on a new sheet:
- put your department names down the left in column A
- put your practices across the top in row 1
- in B2, enter
=SUMPRODUCT((Sheet1!$A$1:$A$100=$A2)*(Sheet1!$C$1: $C$100)=B$1)*(Sheet1!$B$1:$B$100))
- use the fill handle to drag that formula across and down
- I assumed your data is in rows 2 through 100...change to suit your data set

-KC
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Rich Mogy" wrote:

I have a table with Department, Practice and various amounts

For each department, practice combination I want to count the number of
amounts between 500-1000, 1001-1500, 1501-2000, etc.

I tried FREQUENCY, but found it fine when all I had to do was count for one
departments, but now that I need department/practice combinations, it seems
to be too cumbersome.

Thanks in advance.

Sample of data where column 1 is department, column 3 is practice and column
2 is amount

01
$3,000.00
17

01
$818.90
17

01
$1,098.20
17

01
$500.00
999

01
$500.00
999

01
$500.00
999

01
$540.00
999

01
$593.80
999

01
$628.52
999

01
$797.34
999

01
$797.34
999

01
$797.34
999

01
$1,157.35
999

01
$750.00
999

01
$1,343.25
999

01
$1,000.00
999

01
$3,355.80
999

01
$616.50
999

02
$540.00
4

02
$516.95
4

02
$538.89
4

02
$1,240.32
5

COUNT IF
 

I didn't make myself clear --

I want the count of the number of items by department/practice combination
between 500-1000, 1001-1500, 1501-2000, 2001-2500, 2501-3000 etc. So
department 01, practice 17 has three items, 1 in the 3000 range, 1 in the
1001-1500 range and 1 in the 500-1000 range.

Department 999 has 15 items 11 in the 500-1000, 2 in the 1001-1500 and 1 in
3000.

FREQUENCY would work, but is too labor intensive, so I thought if there was
something like DFREQUENCY, or some other way to do this, that would be
great.


"KC Rippstein hotmail com" <kcrippstein<atdot wrote in message
...
SUMPRODUCT is used for multiple criteria.
If you set up your report on a new sheet:
- put your department names down the left in column A
- put your practices across the top in row 1
- in B2, enter
=SUMPRODUCT((Sheet1!$A$1:$A$100=$A2)*(Sheet1!$C$1: $C$100)=B$1)*(Sheet1!$B$1:$B$100))
- use the fill handle to drag that formula across and down
- I assumed your data is in rows 2 through 100...change to suit your data
set

-KC
--
Please remember to indicate when the post is answered so others can
benefit
from it later.


"Rich Mogy" wrote:

I have a table with Department, Practice and various amounts

For each department, practice combination I want to count the number of
amounts between 500-1000, 1001-1500, 1501-2000, etc.

I tried FREQUENCY, but found it fine when all I had to do was count for
one
departments, but now that I need department/practice combinations, it
seems
to be too cumbersome.

Thanks in advance.

Sample of data where column 1 is department, column 3 is practice and
column
2 is amount

01
$3,000.00
17

01
$818.90
17

01
$1,098.20
17

01
$500.00
999

01
$500.00
999

01
$500.00
999

01
$540.00
999

01
$593.80
999

01
$628.52
999

01
$797.34
999

01
$797.34
999

01
$797.34
999

01
$1,157.35
999

01
$750.00
999

01
$1,343.25
999

01
$1,000.00
999

01
$3,355.80
999

01
$616.50
999

02
$540.00
4

02
$516.95
4

02
$538.89
4

02
$1,240.32
5

COUNT IF
 

I figured it out --thanks for the input it does exactly what I needed.

RM
"KC Rippstein hotmail com" <kcrippstein<atdot wrote in message
...
SUMPRODUCT is used for multiple criteria.
If you set up your report on a new sheet:
- put your department names down the left in column A
- put your practices across the top in row 1
- in B2, enter
=SUMPRODUCT((Sheet1!$A$1:$A$100=$A2)*(Sheet1!$C$1: $C$100)=B$1)*(Sheet1!$B$1:$B$100))
- use the fill handle to drag that formula across and down
- I assumed your data is in rows 2 through 100...change to suit your data
set

-KC
--
Please remember to indicate when the post is answered so others can
benefit
from it later.


"Rich Mogy" wrote:

I have a table with Department, Practice and various amounts

For each department, practice combination I want to count the number of
amounts between 500-1000, 1001-1500, 1501-2000, etc.

I tried FREQUENCY, but found it fine when all I had to do was count for
one
departments, but now that I need department/practice combinations, it
seems
to be too cumbersome.

Thanks in advance.

Sample of data where column 1 is department, column 3 is practice and
column
2 is amount

01
$3,000.00
17

01
$818.90
17

01
$1,098.20
17

01
$500.00
999

01
$500.00
999

01
$500.00
999

01
$540.00
999

01
$593.80
999

01
$628.52
999

01
$797.34
999

01
$797.34
999

01
$797.34
999

01
$1,157.35
999

01
$750.00
999

01
$1,343.25
999

01
$1,000.00
999

01
$3,355.80
999

01
$616.50
999

02
$540.00
4

02
$516.95
4

02
$538.89
4

02
$1,240.32
5