Linking Checkboxes/Drop Down Lists to a Formula

[Ryan F]

Hey guys, back with another question.

I am currently making a spread sheet to determine what percentage of phones
we get back are of the following nature:

Under Warranty
No Trouble Found
Customer Abuse

I would like to make a drop down list, so our techs can select only one of
those three options (which I know how to do).

Is there a way to link those options into another cell and keep a running
tally of how many of each were chosen? I can go from there and format the
percentages etc...

Happy Friday!
-Ryan

 

[Luke M]

To do this, I'm assuming your gerenating a table/list of all your entries
(your drop down list is via data validation?)

You could then simply do a something like
=COUNTIF(A2:A100,"Under Warranty")
To find out how many of each things were entered.

OR...
If you've gone with objects from the form menu (combo boxes/check boxes,
etc) you could also link these to a cell somewhere (probably hidden). Since
these would generate numerical values, simply change the COUNTIF to look for
numbers, such as:
=COUNTIF(A2:A100,1)

 

[R. Ford]

Thank you very much Luke, now that that works... let me expand here.

So in column F I have the drop-down list for type of return and in column E
I have a drop-down list of 12 customers we take returns from.

The COUNTIF works great, but is there anyway I could break it down by
customer.

The tech chooses the customer and type of return, then off to the side I
have the 12 customers listed and under each one Abuse/Damage, No Trouble
Found, Under Warranty.

Basically, is there a way to use 2 sets of data (Customer/Type of return)
and still use COUNTIF?

 

[Luke M]

Yes and No. Yes, it can be done, but not with countif. Need something more
powerful like

=SUMPRODUCT((F2:F100="Under Warranty")*(E2:E100="Customer1"))

Notes:
Prior to 2007, can't use entire column with SUMPRODUCT (no F:F)
Arrays MUST be same size.

 

[Shane Devenshire]

Hi,

In 2007 you can use:

=COUNTIFS(F2:F100,"Under Warranty",E2:E100,"Customer1")
or
=COUNTIFS(F2:F100,H1,E2:E100,I1)
in this second case you enter the reason in H1 and the customer in I1.