I'm making a spreadsheet of 3 employees that will display what they have sold
to each different customer they talk to. In one column I have the
salesperson, identified as a number, and in the next column I have
Warranties. So if they sold a warranty to that person the price will be
typed in.

I have used the Countif function to count the number of people each sales
person has seen w/the formula, =COUNTIF(A7:A102,"1") .. where the sales
person is identified by 1.

I used the Sumif function to count the total warranty revenue by each sales
person w/the formula =SUMIF(A7:A102,"1",E7:E102).. where the sales person is
1 and column E contains the revenue.

But I also need to be able to count the total warranties sold by each sales
person. So if salesperson "1" has sold 8 warranties the formula will =8.

Any help is greatly appreciated! Thanks.

I think you want SUMPRODUCT.

=SUMPRODUCT(--(A7:A102="1"),--(B7:B102="Yes"))

In the above formula, it assumes that a warranty sold is indicated by "Yes"
in column B. You can adjust this to meet the actual criteria.

Does that do what you want?
Regards,
Paul

--

"Clay" wrote in message
...
I'm making a spreadsheet of 3 employees that will display what they have
sold
to each different customer they talk to. In one column I have the
salesperson, identified as a number, and in the next column I have
Warranties. So if they sold a warranty to that person the price will be
typed in.

I have used the Countif function to count the number of people each sales
person has seen w/the formula, =COUNTIF(A7:A102,"1") .. where the sales
person is identified by 1.

I used the Sumif function to count the total warranty revenue by each
sales
person w/the formula =SUMIF(A7:A102,"1",E7:E102).. where the sales person
is
1 and column E contains the revenue.

But I also need to be able to count the total warranties sold by each
sales
person. So if salesperson "1" has sold 8 warranties the formula will =8.

Any help is greatly appreciated! Thanks.

Thanks for the quick response.. I have the cost of the warranty sold in the
warranty column, and they are all different.

I just tried =SUMPRODUCT(--(A7:A102="1"),--(E7:E102="0")) but its coming up
as 0.

Any more suggestions? And thanks again for the help!

"PCLIVE" wrote:

I think you want SUMPRODUCT.

=SUMPRODUCT(--(A7:A102="1"),--(B7:B102="Yes"))

In the above formula, it assumes that a warranty sold is indicated by "Yes"
in column B. You can adjust this to meet the actual criteria.

Does that do what you want?
Regards,
Paul

--

"Clay" wrote in message
...
I'm making a spreadsheet of 3 employees that will display what they have
sold
to each different customer they talk to. In one column I have the
salesperson, identified as a number, and in the next column I have
Warranties. So if they sold a warranty to that person the price will be
typed in.

I have used the Countif function to count the number of people each sales
person has seen w/the formula, =COUNTIF(A7:A102,"1") .. where the sales
person is identified by 1.

I used the Sumif function to count the total warranty revenue by each
sales
person w/the formula =SUMIF(A7:A102,"1",E7:E102).. where the sales person
is
1 and column E contains the revenue.

But I also need to be able to count the total warranties sold by each
sales
person. So if salesperson "1" has sold 8 warranties the formula will =8.

Any help is greatly appreciated! Thanks.

Don't use quotes: =SUMPRODUCT(--(A7:A102=1),--(E7:E1020))

"Clay" wrote:

Thanks for the quick response.. I have the cost of the warranty sold in the
warranty column, and they are all different.

I just tried =SUMPRODUCT(--(A7:A102="1"),--(E7:E102="0")) but its coming up
as 0.

Any more suggestions? And thanks again for the help!

"PCLIVE" wrote:

I think you want SUMPRODUCT.

=SUMPRODUCT(--(A7:A102="1"),--(B7:B102="Yes"))

In the above formula, it assumes that a warranty sold is indicated by "Yes"
in column B. You can adjust this to meet the actual criteria.

Does that do what you want?
Regards,
Paul

--

"Clay" wrote in message
...
I'm making a spreadsheet of 3 employees that will display what they have
sold
to each different customer they talk to. In one column I have the
salesperson, identified as a number, and in the next column I have
Warranties. So if they sold a warranty to that person the price will be
typed in.

I have used the Countif function to count the number of people each sales
person has seen w/the formula, =COUNTIF(A7:A102,"1") .. where the sales
person is identified by 1.

I used the Sumif function to count the total warranty revenue by each
sales
person w/the formula =SUMIF(A7:A102,"1",E7:E102).. where the sales person
is
1 and column E contains the revenue.

But I also need to be able to count the total warranties sold by each
sales
person. So if salesperson "1" has sold 8 warranties the formula will =8.

Any help is greatly appreciated! Thanks.

Bob Phillips formula is the best way to go by using the ISNUMBER function.

So going with what you currently have, adjust your formula as follows.
=SUMPRODUCT(--(A7:A102="1"),--(ISNUMBER(E7:E102)))

The ISNUMBER function determines if the values in the range are numbers as
opposed to text. Zero is not counted with this.

Keep this in mind. If you are using actual numbers to represent sales
people, then you won't include quotes around it as that would be text.
Since your COUNT formulas were working, I assumed that you used the number
one just as an example.

HTH,
Paul



--

"Clay" wrote in message
...
Thanks for the quick response.. I have the cost of the warranty sold in
the
warranty column, and they are all different.

I just tried =SUMPRODUCT(--(A7:A102="1"),--(E7:E102="0")) but its coming
up
as 0.

Any more suggestions? And thanks again for the help!

"PCLIVE" wrote:

I think you want SUMPRODUCT.

=SUMPRODUCT(--(A7:A102="1"),--(B7:B102="Yes"))

In the above formula, it assumes that a warranty sold is indicated by
"Yes"
in column B. You can adjust this to meet the actual criteria.

Does that do what you want?
Regards,
Paul

--

"Clay" wrote in message
...
I'm making a spreadsheet of 3 employees that will display what they
have
sold
to each different customer they talk to. In one column I have the
salesperson, identified as a number, and in the next column I have
Warranties. So if they sold a warranty to that person the price will
be
typed in.

I have used the Countif function to count the number of people each
sales
person has seen w/the formula, =COUNTIF(A7:A102,"1") .. where the sales
person is identified by 1.

I used the Sumif function to count the total warranty revenue by each
sales
person w/the formula =SUMIF(A7:A102,"1",E7:E102).. where the sales
person
is
1 and column E contains the revenue.

But I also need to be able to count the total warranties sold by each
sales
person. So if salesperson "1" has sold 8 warranties the formula will
=8.

Any help is greatly appreciated! Thanks.

Paul,

Be aware that COUNTIF will count numeric values even if embedded with
quotes. SUMPRODUCT won't.

A1:A4: 1,1,1,1

=COUNTIF(A1:A4,"1") returns 4

=SUMPRODUCT(--(A1:A4="1")) returns 0

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"PCLIVE" wrote in message
...
Bob Phillips formula is the best way to go by using the ISNUMBER function.

So going with what you currently have, adjust your formula as follows.
=SUMPRODUCT(--(A7:A102="1"),--(ISNUMBER(E7:E102)))

The ISNUMBER function determines if the values in the range are numbers as
opposed to text. Zero is not counted with this.

Keep this in mind. If you are using actual numbers to represent sales
people, then you won't include quotes around it as that would be text.
Since your COUNT formulas were working, I assumed that you used the number
one just as an example.

HTH,
Paul



--

"Clay" wrote in message
...
Thanks for the quick response.. I have the cost of the warranty sold in
the
warranty column, and they are all different.

I just tried =SUMPRODUCT(--(A7:A102="1"),--(E7:E102="0")) but its coming
up
as 0.

Any more suggestions? And thanks again for the help!

"PCLIVE" wrote:

I think you want SUMPRODUCT.

=SUMPRODUCT(--(A7:A102="1"),--(B7:B102="Yes"))

In the above formula, it assumes that a warranty sold is indicated by
"Yes"
in column B. You can adjust this to meet the actual criteria.

Does that do what you want?
Regards,
Paul

--

"Clay" wrote in message
...
I'm making a spreadsheet of 3 employees that will display what they
have
sold
to each different customer they talk to. In one column I have the
salesperson, identified as a number, and in the next column I have
Warranties. So if they sold a warranty to that person the price will
be
typed in.

I have used the Countif function to count the number of people each
sales
person has seen w/the formula, =COUNTIF(A7:A102,"1") .. where the
sales
person is identified by 1.

I used the Sumif function to count the total warranty revenue by each
sales
person w/the formula =SUMIF(A7:A102,"1",E7:E102).. where the sales
person
is
1 and column E contains the revenue.

But I also need to be able to count the total warranties sold by each
sales
person. So if salesperson "1" has sold 8 warranties the formula will
=8.

Any help is greatly appreciated! Thanks.

Oops! You are correct. I was hung up on SUMPRODUCT for some reason.

Thanks for pointing that out.

--

"Bob Phillips" wrote in message
...
Paul,

Be aware that COUNTIF will count numeric values even if embedded with
quotes. SUMPRODUCT won't.

A1:A4: 1,1,1,1

=COUNTIF(A1:A4,"1") returns 4

=SUMPRODUCT(--(A1:A4="1")) returns 0

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"PCLIVE" wrote in message
...
Bob Phillips formula is the best way to go by using the ISNUMBER
function.

So going with what you currently have, adjust your formula as follows.
=SUMPRODUCT(--(A7:A102="1"),--(ISNUMBER(E7:E102)))

The ISNUMBER function determines if the values in the range are numbers
as opposed to text. Zero is not counted with this.

Keep this in mind. If you are using actual numbers to represent sales
people, then you won't include quotes around it as that would be text.
Since your COUNT formulas were working, I assumed that you used the
number one just as an example.

HTH,
Paul



--

"Clay" wrote in message
...
Thanks for the quick response.. I have the cost of the warranty sold in
the
warranty column, and they are all different.

I just tried =SUMPRODUCT(--(A7:A102="1"),--(E7:E102="0")) but its
coming up
as 0.

Any more suggestions? And thanks again for the help!

"PCLIVE" wrote:

I think you want SUMPRODUCT.

=SUMPRODUCT(--(A7:A102="1"),--(B7:B102="Yes"))

In the above formula, it assumes that a warranty sold is indicated by
"Yes"
in column B. You can adjust this to meet the actual criteria.

Does that do what you want?
Regards,
Paul

--

"Clay" wrote in message
...
I'm making a spreadsheet of 3 employees that will display what they
have
sold
to each different customer they talk to. In one column I have the
salesperson, identified as a number, and in the next column I have
Warranties. So if they sold a warranty to that person the price will
be
typed in.

I have used the Countif function to count the number of people each
sales
person has seen w/the formula, =COUNTIF(A7:A102,"1") .. where the
sales
person is identified by 1.

I used the Sumif function to count the total warranty revenue by each
sales
person w/the formula =SUMIF(A7:A102,"1",E7:E102).. where the sales
person
is
1 and column E contains the revenue.

But I also need to be able to count the total warranties sold by each
sales
person. So if salesperson "1" has sold 8 warranties the formula will
=8.

Any help is greatly appreciated! Thanks.

=SUMPRODUCT(--(A7:A102=1),--(ISNUMBER(B7:B102)))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Clay" wrote in message
...
I'm making a spreadsheet of 3 employees that will display what they have
sold
to each different customer they talk to. In one column I have the
salesperson, identified as a number, and in the next column I have
Warranties. So if they sold a warranty to that person the price will be
typed in.

I have used the Countif function to count the number of people each sales
person has seen w/the formula, =COUNTIF(A7:A102,"1") .. where the sales
person is identified by 1.

I used the Sumif function to count the total warranty revenue by each
sales
person w/the formula =SUMIF(A7:A102,"1",E7:E102).. where the sales person
is
1 and column E contains the revenue.

But I also need to be able to count the total warranties sold by each
sales
person. So if salesperson "1" has sold 8 warranties the formula will =8.

Any help is greatly appreciated! Thanks.

thanks for all the help guys!

"Bob Phillips" wrote:

=SUMPRODUCT(--(A7:A102=1),--(ISNUMBER(B7:B102)))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Clay" wrote in message
...
I'm making a spreadsheet of 3 employees that will display what they have
sold
to each different customer they talk to. In one column I have the
salesperson, identified as a number, and in the next column I have
Warranties. So if they sold a warranty to that person the price will be
typed in.

I have used the Countif function to count the number of people each sales
person has seen w/the formula, =COUNTIF(A7:A102,"1") .. where the sales
person is identified by 1.

I used the Sumif function to count the total warranty revenue by each
sales
person w/the formula =SUMIF(A7:A102,"1",E7:E102).. where the sales person
is
1 and column E contains the revenue.

But I also need to be able to count the total warranties sold by each
sales
person. So if salesperson "1" has sold 8 warranties the formula will =8.

Any help is greatly appreciated! Thanks.