From text to number...

Gavin Hyde

New Member
Joined
Jul 18, 2002
Messages
19
Hi there,

I was wondering if anyone could tell me how I can use a drop down list to reference another spreadsheet and leave a numerical value.

For example: You have a list box that has A,B,C in the list. The list box is present in say 30 cells in a column on the first sheet, you choose B from the list and on another sheet you have a cell that counts your choice, in this case B, as 1. So if you choose B 3 times and A 4 times it will count those for you on the second sheet in a cell that represents each of the choices ie A,B,C. The purpose of which is to see how many times that selection was made...

I hope I have explained well enough... Please feel free to ask for clarification and thanks in advance for any help you can offer.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi Gavin

I'm not sure I understood well. If I didn't understand the question, please try to explain it in another way.

What I understood is that in a worksheet you want to count how many times an option was chosen in another worksheet.

For example, let's say that the cells A1:A30 in the worksheet "Sheet1" can have the values "A", "B" or "C".

Then in the worksheet "Sheet2" if you want to count how may "B"s were chose write in a cell:

=COUNTIF('Sheet1'!A1:A30,"B")

Hope this helps
PGC
 

Gavin Hyde

New Member
Joined
Jul 18, 2002
Messages
19
That actually worked perfectly... Except for one thing... now I have a new issue. I actually need two conditions to be met for the cell to track the correct data. The formula that you posted actually met the second of these. The first would actually be a date.

The date would be kept simple ie a 1 or 21 for the 1st or 21st. So using roughly the same model as before now I need to know how many "B"s were selected on the 1st or "A"s on the 3rd etc.

I currently have something like this....
=COUNTIF(Sheet3!B2:B32,"1") & COUNTIF(Sheet3!G2:G31,"B")
The problem is that I can't remember how to link two conditions. The sample I used above has the "&" in it simply because that is the last thing I tried however wrong it may have been...

Thanks again for your help and if you can help me with this I'll try not to bother you again for a while ;)

Thanks Again,
g
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,242
If you wish to count how many "1" are in range B2:B32 added to how many "B" are in range G2:G31 (or G32?), simply use
=COUNTIF(Sheet3!B2:B32,"1") & COUNTIF(Sheet3!G2:G31,"B")

If you wish something else, please post again.

Bye,
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,242

ADVERTISEMENT

Grrr..
=COUNTIF(Sheet3!B2:B32,"1")+COUNTIF(Sheet3!G2:G31,"B")

Bye,
 

Gavin Hyde

New Member
Joined
Jul 18, 2002
Messages
19
Thanks for your reply, but I don't want to add the two together. There is a count that needs to take place but sub totals rather than a grand total...

For example I need the sheet to tell me how many "A"s were submitted on the 1st. So in order to do this I've chosen to keep it a little more simple by making my date field a list from which you would choose the day of the month. So there is a drop down and you choose the correct number 1-31.

I need a count of each of the "A"s on the any given day, the "B"s on any given date and so on.

So the formula in "English" would be something like "if the date field has a 1 in it and the letter field has a A in it display that total here" OR "display the total number of "A"s from the 1st", - "B"s from the 2nd, "C"s from the 23rd etc.

Thanks for your efforts and I apologize if I am not explaining clearly.
g
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

Hi again Gavin

COUNTIF can only be used with one condition. If you have 2 conditions use SUMPRODUCT.

Please try:

=SUMPRODUCT(--(Sheet3!B2:B31="1"),--(Sheet3!G2:G31="B"))

Hope this solves your problem
PGC
 

Gavin Hyde

New Member
Joined
Jul 18, 2002
Messages
19
Below is the actual formula that I have on the spreadsheet based on the help I've received plus what I'm trying to do...

=SUMPRODUCT(--(Sheet3!B2:B31="1"),--(Sheet3!G2:G31="Bernhoft"))

You can see the word (name) Bernhoft is in the formula, this is because A,B or C actually equals a name.

Unfortunately this did not work. No matter what changes I make in the cells on sheet3 the values in the cell (with the formula) on sheet2 remains at 0 (zero).

Bear in mind that the value "Bernhoft" comes from a list of about 21 names that is done through a drop box as added from the data>validation menu. The value "1" is also from a list in a drop box, in this case 1-31. I don't know if this changes the effect of the formula.

Could it be the "--" in the formula? Should this be a single or a long hyphen instead?
 

Gavin Hyde

New Member
Joined
Jul 18, 2002
Messages
19
Just to further clarify what we should have on sheet2 is the amount of orders for a specific name on a given day ie Bernhoft has 12 on the 1st, Buckley has 9 on the 3rd and so on...

Sheet2 is set up on a grid with numbers (date) going across the top and Names going down the left side. The idea is that order data will be filled out on sheet3 and those orders will be counted on sheet2. The intended purpose is to track productivity while saving time and avoiding errors.

Hope this helps...

Thanks for your efforts.
g
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
You probably don't need the quotes around the 1 - [in general text needs quotes, numbers don't]. Try

=SUMPRODUCT(--(Sheet3!B2:B31=1),--(Sheet3!G2:G31="Bernhoft"))
 

Forum statistics

Threads
1,141,734
Messages
5,708,168
Members
421,549
Latest member
Dtcfire

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top