Need an OR Function that will allow for Text vales.........

[nevi]

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

[SteveG]

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

[Peo Sjoblom]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[nevi]

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