# Solved: COUNTIF & Drop-down menus (Excel)



## gevans (Aug 26, 2004)

Hello: I am using a "countif" formula & it works great for 1 criteria - but I want a count count only if 2 criteria are met (in 2 different columns) - the formula I am using is:

=COUNTIF($A$18:$A$99,"NSY")+COUNTIF($D$18:$D$99,"01") 

It doesn't seem to work correctly - what am I doing wrong, any suggestions?

Also, I have an excel file saved on a shared drive - which includes drop-down menus. When my coworker opened it on her computer - the dropdown menus were not there. How do I ensure the formats/dropdown menus appear no matter where the file is opened?


----------



## bomb #21 (Jul 1, 2005)

=SUMPRODUCT(($A$18:$A$99="NSY")*($D$18:$D$99="01"))

(assuming the "01"s are text).

For the second issue, tell us how you set up the drop-down menus.


----------



## DKTaber (Oct 26, 2001)

Gevans: Can't comment on the dropdown menus, but I set up a worksheet with some "NSY" entries among others that were something else in one column, and in a different column, entered some "01" and other entries, then created a formula similar to yours and it worked as designed. However, to make it work, the column containing the "01"s must be formatted as text (else entering "01" actually gets entered as "1" and it's by default a number). I can't believe it's that simple, but since nobody replied to you, thought I'd take a shot.


----------



## bomb #21 (Jul 1, 2005)

I believe gevans wants a count of rows where col A = NSY *and* col D = "01".

So with A18 = "NSY" & D18 = "01", and A19 = "NSZ" & D19 = "01", then:

=COUNTIF($A$18:$A$98,"NSY")+COUNTIF($D$18:$D$98,"01")

returns 3. But 1 is the required result (I believe).


----------



## Zack Barresse (Jul 25, 2004)

Hello,

I believe Andy has your count solution for you. What about your drop down menus? How are those created? What is their purpose? What is the scope of this application? What are the differences between you and your co-workers computers?


----------



## bomb #21 (Jul 1, 2005)

Hi Zack.  Shared workbook? (gotta run -- TTYL).


----------



## gevans (Aug 26, 2004)

I set up a drop down menu in column A with a validation list of 11 entries (data/validatation). I did this so that when I used a COUNTIF formula in the 2nd worksheet I would get an accurate count if column "A" was limited to only specific entries (less chance of entry error). This works fine when I open it under my username & login - the problem happens when a co-worker opens the same file (on a shared drive). When they open it - there are no dropdown menus.

This is the most detailed spreadsheet I've done in a long time and I must say - I am learning a lot through you guys!


----------



## Zack Barresse (Jul 25, 2004)

So are your versions the same? What are the differences between your setup? Have you checked on this file with other computers?


----------



## gevans (Aug 26, 2004)

Ah haa!! I am using a newer version of Excel than she is!! We are in the process of updating her version of the software. Also - as far as the "differences in setup" - what exactly should I be looking for?


----------



## Zack Barresse (Jul 25, 2004)

Well, that may or may not be it. I would check it on another pc of similarity to yours and another one to hers. What I meant by "setup" was computer configuration. One key point being version differences. Could you perhaps post a sample of the file so we may try it as well?


----------



## gevans (Aug 26, 2004)

Here is a sample file - with all changes to date. As you can see, the "database" sheet will be the only one that requires data entry (other than a title change & notes in a column in sheet 2) - while the other two sheets (I hope) with automatically update based on data in sheet 1. The only other thing I need it to do is: allow a filter for a date range on the "database" sheet so that only selected records are pulled into the 2nd & 3rd sheet. I tried a filter - but the records would disappear!! Have I explained all this correctly???? Any suggestions are greatly appreciated!!! All those so far are working great!!

Signed,
"In over my head!!!"


----------



## gevans (Aug 26, 2004)

As you can see in the attached file, the "database" sheet will be the main entry point & the only one that requires data entry (other than a title change on occasion & additional notes in a column in sheet 2) - while the other two sheets (I hope) will automatically update based on data entered in sheet 1. 

The only other thing I need it to do is: allow a filter for a date range on the "database" sheet so that only selected records are pulled into the 2nd & 3rd sheet. I tried a filter - but the records would disappear!! Have I explained all this correctly???? Any suggestions are greatly appreciated!!! All those given so far are working great!!

Signed,
"A little in over my head!!!"


----------



## OBP (Mar 8, 2005)

Filtering by 2 dates can be done using VBA code, but this database would make a lot more sense in Access.


----------



## Zack Barresse (Jul 25, 2004)

Now gevans, why in the world would you be double posting?

...

http://forums.techguy.org/business-applications/533642-countif-drop-down-menus-excel.html


----------



## gevans (Aug 26, 2004)

...ooops! Am I bad?? I guess when I didn't get a reply - I assumed I needed to post it again! My mistake!


----------

