help - count code in worksheet1 & place count in worksheet2 next to appropriate code#

[burnettec]

Column A (range "NAICS07") & B (range "NAICS12") in worksheet "TEST" contain a list of codes.
Column E (range "NAICSwhisardViol") in worksheet "violators" is also a list of codes, but may contain replicates of one or more codes.
I want to count the number of times a code in ranges "NAICS07" or "NAICS12" shows up in range "NAICSwhisardViol" for a particular Fiscal Year (Column B range "FiscalYearViol" in worksheet "violators").
I want the total count for a particular code per designated fiscal year to be placed in the appropriate cell in columns L or M of worksheet "TEST".
If a particular code doesn't show up in the "violators" worksheet, I want to return a value of "" (empty) in the appropriate cell in Column L or M of worksheet "TEST".
Could anyone out there help me with this? Thank you!!
PRACTICE.xlsm
p.s. I have tried SUMPRODUCT(('violators'!D:D=A7)*('violators'!A:A="2010")) in cell L7 of "TEST" but it doesn't work when applied to all cells in that column. In my function field Excel 2010 deletes the single quotes around 'violators' when I hit return and I don't know why.

12/30/2012: note -- another question coming out of this original semi-solved question was posted at http://www.mrexcel.com/forum/excel-q...eria-help.html on 12/29/2012 in the 11am hour. It has been partially, but not completely solved as of 4pm 12/30/2012. Here's the status:
HOW TO COUNT THE NUMBER OF VIOLATORS BY NAICS CODE in one worksheet, and place the total count in the appropriate cell corresponding to that NAICS in a different worksheet

CODES TO MATCH ARE IN 1 COLUMN
=COUNTIFS(NAICSwhisardViol, B11, FiscalYEAR,"2010")
=COUNTIFS(NAICSwhisardViol, B11, FiscalYEAR,"2011")

CODES TO MATCH ARE IN either one or the other column of 2 DIFFERENT COLUMNS (NAICS07 & NAICS12)
=COUNTIFS(NAICSwhisardViol, IF(ISBLANK(B11)=TRUE,C11,B11), FiscalYEAR,"2010")
=COUNTIFS(NAICSwhisardViol, IF(ISBLANK(B11)=TRUE,C11,B11), FiscalYEAR,"2011")

=SUM(IF((NAICSwhisardViol=A7)+(NAICSwhisardViol=B7), IF(FiscalYEAR=2011,1)))…THIS IS A BAD ONE.
=SUM(IF((NAICSwhisardViol=A7)+(NAICSwhisardViol=B7), IF(FiscalYEAR="2011",1)))…THIS IS A BAD ONE TOO.
We are trying to figure out if my computer doesn’t handle array functions because it doesn’t have enough memory.
I can’t get any related SUMPRODUCT functions to work either.

12/30/2012 5:30pm: THIS POST IS AS SOLVED AS IT CAN GET. THE ISSUE WITH SUMIF and SUMPRODUCT cannot be solved at this time and time has run out for me. Am going to stick with COUNTIFS functions above. THANKS EVERYONE!

[FDibbins]

try using countifS()

[burnettec]

THank you for your suggestion. I got it to work with:
=COUNTIFS(PRACTICE.xlsm!NAICSwhisardViol, A7, PRACTICE.xlsm!FiscalYEAR,"2010") in L7/sheet TEST
=COUNTIFS(PRACTICE.xlsm!NAICSwhisardViol, A7, PRACTICE.xlsm!FiscalYEAR,"2011") in M7/sheet TEST
I'm not too skilled in SUMPRODUCT yet.
Thanks, again!

[burnettec]

In my solution :
=COUNTIFS(PRACTICE.xlsm!NAICSwhisardViol, A7, PRACTICE.xlsm!FiscalYEAR,"2010")
what is the proper syntax to change A7 to be 'A7 or B7'?

[FDibbins]

not sure I understand what you want? do you mean you want text A7 instead of cell reference A7?

[burnettec]

No, sorry. I mean the code# in range "NAICSwhisardViol" can match the code in either cell A7 or B7.
(The spreadsheets I've uploaded to this forum are incomplete, so it only looks like A7/B7 always have matching values, but they don't in bigger database.)

[burnettec]

SOrry about duplicate entry in forum, but I didn't think I could ask another question on this thread once I marked it SOLVED.
Will do better next time.

[FDibbins]

could you upload your file again please, so i can see what you mean? give a few examples of what you want the outcome to be, even if you have to enter the "answer" manually

[burnettec]

No problem! Thank you for following up on my question. I am attaching an updated version of my last file. I have included more of my list of codes in Column A (range "NAICS07") & B (range "NAICS12") of Sheet"TEST" in order for you to better understand my last question. You can see that there may be blank cells in one of the two columns at a given time, so I need my function to look through them both for a matching number in Column E (range "NAICSwhisardViol") in worksheet "violators". My updated file also includes a new worksheet called "TESTcomplete1" which is my first version of a completed TEST. The function I created only considers Column A (range "NAICS07"), so I need some sort of "OR" statement which considers Column B (range "NAICS12") in my current functions:
=COUNTIFS(PRACTICE.xlsm!NAICSwhisardViol, A7, PRACTICE.xlsm!FiscalYEAR,"2010") in L7/sheet TEST
=COUNTIFS(PRACTICE.xlsm!NAICSwhisardViol, A7, PRACTICE.xlsm!FiscalYEAR,"2011") in M7/sheet TEST
I'm not sure what the syntax is for that addition.
PRACTICE2.xlsm
Thanks very much for your help!

[jeffreybrown]

Is this a cross post?

http://www.mrexcel.com/forum/excel-q...eria-help.html

[burnettec]

Yes, it is related to the same workbook. Am I allowed to have related questions in two different forums, or do you need to lock this one out?

[jeffreybrown]

Here is the canned message we always post for a cross posts and "no" it won't be locked. Most forums follow the same guidance.

I'm afraid your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

Between the two posts the post count stands at close to 50, so I'm sure everybody involved would love to know where this same question is being asked.

[FDibbins]

As noticed by Jeff, you have cross-posted the same question in MrExcel. While that is not against any rules of this forum, you are required to paste the link here to any other forums you have asked the same question on.

I will continue on this thread with you once this has been cleared up, thanks

[burnettec]

OK! I've reread the forum rules and understand where you're coming from. I'll post links next time if the situation happens again. More questions:
I'm not supposed to private-message a moderator for specific questions about the forum, correct?
How many DIFFERENT questions am I allowed to post about the same project in the Excel Forum site?
How many DIFFERENT questions am I allowed to post about the same project in the same forum (i.e. Excel vs Excel VBA)?
thanks!!