Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need an OR Function that will allow for Text vales.........
I don't think you could call me a beginner with Escal, but I am certainly not an advanced user either. I have a tracker that I need to encorporate an OR structure to, and it is not working. In a nut shell, C4:C115 give a drop downlist with several options, one of them being "Aircard". B4:B115 also gives a drop down list with 30 possible selction of names. What I need is an OR structure that will count how many times "Aircard" is selected in cells C4:C115, but only if the name in the corosponding cell is one of 17 names, out of the possible 30. I can get it to work comparing against one name, but not against multiple. My boss is breathing down my neck to have this tracked done yesterday. PLEASE HELP!!! :) -- nevi ------------------------------------------------------------------------ nevi's Profile: http://www.excelforum.com/member.php...o&userid=33238 View this thread: http://www.excelforum.com/showthread...hreadid=530594 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need an OR Function that will allow for Text vales.........
Nevi, I think you're easiest solution would be to use a PivotTable. Starting with your column headers, select your range, let's say B3:C115. Click on the Pivot Table wizard. In step 1, click on Next. On Step 2 click on next. On step 3, click on layout. Click and drag your column C header to the Row area then your column B header. Click and drag the column B header to the Data area. This should say "Count of" and your header name. If it says something else, double click on it and switch to Count. Click on OK. Then you can select where you want the Pivot table to be placed. Click Finish. The pivot table will have drop down lists that you can choose from for each column header of your source data. Just go in and de-select "Show All" and select "Aircard" from the appropriate list. Do the same with the list of associated names but selecting the 17 you want to see after de-selecting "Show All". Does that help? Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=530594 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need an OR Function that will allow for Text vales.........
Put the 17 names in a contigeous range like G1:W1 coing across, then you can
use =SUMPRODUCT((C4:C115="aircard")*(B4:B115=G1:W1)) or hardcoded =SUMPRODUCT((C4:C115="aircard")*(B4:B115={"name1", "name2","name3","name4","name5","name6","name7 ", "name8","name9","name10","name11","name12","name13 ","name14","name15","name16","name17"})) -- Regards, Peo Sjoblom http://nwexcelsolutions.com "nevi" wrote in message ... I don't think you could call me a beginner with Escal, but I am certainly not an advanced user either. I have a tracker that I need to encorporate an OR structure to, and it is not working. In a nut shell, C4:C115 give a drop downlist with several options, one of them being "Aircard". B4:B115 also gives a drop down list with 30 possible selction of names. What I need is an OR structure that will count how many times "Aircard" is selected in cells C4:C115, but only if the name in the corosponding cell is one of 17 names, out of the possible 30. I can get it to work comparing against one name, but not against multiple. My boss is breathing down my neck to have this tracked done yesterday. PLEASE HELP!!! :) -- nevi ------------------------------------------------------------------------ nevi's Profile: http://www.excelforum.com/member.php...o&userid=33238 View this thread: http://www.excelforum.com/showthread...hreadid=530594 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need an OR Function that will allow for Text vales.........
YOU GUYS ARE AWESOME!!!!!! I have been staring at that stupid code for 2 friggin days!!! I figured I'd try the SUMPRODUCT(which I had never heard of before now) before the pivot table, as I didn't fully understand how either workd and SUMPRODUCT gave me a copy/paste solution, and it worked like a charm!!!! Thank you sooooooo much. You guys ROCK!!! ;) -- nevi ------------------------------------------------------------------------ nevi's Profile: http://www.excelforum.com/member.php...o&userid=33238 View this thread: http://www.excelforum.com/showthread...hreadid=530594 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a Custom Excel Function to Calculate Gini Coefficients | Excel Worksheet Functions | |||
Date & Time | New Users to Excel | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |