Solved: COUNTIF & Drop-down menus (Excel)

Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

gevans

Thread Starter
Joined
Aug 26, 2004
Messages
99
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?
 
Joined
Jul 1, 2005
Messages
8,546
=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.
 
Joined
Oct 26, 2001
Messages
2,871
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.
 
Joined
Jul 1, 2005
Messages
8,546
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).
 
Joined
Jul 25, 2004
Messages
5,458
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?
 

gevans

Thread Starter
Joined
Aug 26, 2004
Messages
99
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!
 
Joined
Jul 25, 2004
Messages
5,458
So are your versions the same? What are the differences between your setup? Have you checked on this file with other computers?
 

gevans

Thread Starter
Joined
Aug 26, 2004
Messages
99
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?
 
Joined
Jul 25, 2004
Messages
5,458
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

Thread Starter
Joined
Aug 26, 2004
Messages
99
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!!!"
 

Attachments

gevans

Thread Starter
Joined
Aug 26, 2004
Messages
99
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!!!"
 

Attachments

OBP

Joined
Mar 8, 2005
Messages
19,896
Filtering by 2 dates can be done using VBA code, but this database would make a lot more sense in Access.
 

gevans

Thread Starter
Joined
Aug 26, 2004
Messages
99
...ooops! Am I bad?? I guess when I didn't get a reply - I assumed I needed to post it again! My mistake!
 
Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top