Issue devising a formula that counts using multiple criteria

Hello, I have been trying to figure out to get specific results on a file I have been working on with formulas and I am at a complete loss.

The file actually contains two separate worksheets, but I lumped everything onto one in this image. I am also still using Excel 2003.


http://i137.photobucket.com/albums/q...fans/Excel.jpg

What I am having trouble with is creating a formula that uses the YTD Sales from the first table (and excludes zeros or negatives YTD companies) to create a column that lists the occurrences of the SIC Codes for companies with sales over zero.

Also, I'd like to create a chart or table that lists the total $ per sic code (for companies with YTD sales over $0) per state.

Ignore the $ per SIC Code column and the average column. Those I know how to do. I was just getting lazy making this sample.

If anyone can help me figure this out, I would be extremely grateful. I have been messing with this the last couple days and I want to bash my head or my computer against the wall.

Hi

Can you please attach a zipped copy of your example file. Easier to work with than having to build our own interpretation of your picture.


rylo

Thanks. Any help you or anyone else could give me would really be amazing.

Hi

Are the SIC codes in Analysis!K:M always going to be in the same column, or could any code appear in any of the columns?

rylo

Any code can appear in any column it depends on how each company has theirs listed. The first column is their Primary SIC code and the second and third are additional codes linked to their business.

SIC codes are used to mark what kind of industry a company is in.

Try this formula in C2 copied down

=SUMPRODUCT((ANALYSIS!K$2:M$5=A2)* (ANALYSIS!D$2:D$5>0))

For YTD dollars per sic code category per state try variations on

=SUMPRODUCT((ANALYSIS!K$2:M$5=3333)* (ANALYSIS!H$2:H$5="MS")*(ANALYSIS!D$2:D$5))

To give multiple state/sic code combinations you could create a table with states as column headers and sic codes as row headers.