From text to number...

[Gavin Hyde]

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.

 

[pgc01]

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]

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]

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]

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

Bye,

 

[Gavin Hyde]

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]

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]

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]

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]

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