Help with countif and sumif functions

C

Clay

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.
 
P

PCLIVE

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
 
C

Clay

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!
 
B

Bob Phillips

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

--
---
HTH

Bob


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

PCLIVE

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



--
 
B

Bob Phillips

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)
 
P

PCLIVE

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

Thanks for pointing that out.

--
 
C

Clay

thanks for all the help guys!

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

--
---
HTH

Bob


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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sumif & Countif 6
SUMIF Help 4
Sumif And Countif 7
SUMIF with criteria "<>" & "=" 4
averages without zero 5
SUMIF anomaly - Help Needed 2
Counting sales 1
Working with 2 worksheets 1

Top