Help with countif and sumif functions

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

 

[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

 

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

 

[sb1920alk]

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

 

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

 

[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



--

 

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

 

[PCLIVE]

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

Thanks for pointing that out.

--

 

[Clay]

thanks for all the help guys!

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

--
---
HTH

Bob


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