Tech Support Guy banner
Status
Not open for further replies.
1 - 15 of 15 Posts

·
Registered
Joined
·
99 Posts
Discussion Starter · #1 ·
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?
 

·
Registered
Joined
·
8,546 Posts
=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.
 

·
Registered
Joined
·
2,871 Posts
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.
 

·
Registered
Joined
·
8,546 Posts
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).
 

·
Registered
Joined
·
5,458 Posts
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?
 

·
Registered
Joined
·
99 Posts
Discussion Starter · #7 ·
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!
 

·
Registered
Joined
·
99 Posts
Discussion Starter · #9 ·
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?
 

·
Registered
Joined
·
5,458 Posts
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?
 

·
Registered
Joined
·
99 Posts
Discussion Starter · #11 ·
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

·
Registered
Joined
·
99 Posts
Discussion Starter · #12 ·
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

1 - 15 of 15 Posts
Status
Not open for further replies.
Top