CountIF multiple parameters

Karroog · #1 06-18-2009, 10:15 AM

Well not multiple really, only two mind you.

I have a long list of past jobs, around 4000+. I have multiple fields, but I really wish to concentrate on the:

1) Job Type - Example would be Medical or Imprint

2) Job # - Correlates when it was done - Example would be 91059 would be a job within 2008-2009 fiscal year.

All the jobs are listed in the first spreadsheet. The second spreadsheet will hold generalized data broken down by the Type of Job and the fiscal year it was done.

I wish to first count how many jobs fit a specific job type. This was easily done:

=COUNTIF(Data!A2:A4121,A7)

A7 = Medical
A1:A4121 = the range of the names.

Count comes up as 346, which is correct.

I then want to add another parameter to break up the 346 by fiscal year. It ranges from 05-06 to 08-09. I came up with this:

=COUNTIF(Data!A2:A4121,A7)-COUNTIF(Data!F2:F4121,"9????")

Since the job # correlates to the fiscal year it was created, anything starting with 9 is a job done in 08-09. Anything starting with an 8 would be of course 07-08 range. I would have multiple fields with each different fiscal year.

However, when I put the 2nd formula in the function didn't work. It keeps the count at 346, which I know is wrong. I am not sure if I did something wrong here. Been looking at this for an hour and can't figure out what is wrong. The jobs that are medical within 08-09 fiscal year should be 120, but it keeps at 346.

Any help would be superb.

NBVC · #2 06-18-2009, 10:19 AM

is the field starting with "9" text field?

if yes,

=COUNTIF(Data!A2:A4121,A7)-COUNTIF(Data!F2:F4121,"9*")

may work.

Karroog · #3 06-18-2009, 10:21 AM

Quote:

Originally Posted by NBVC

is the field starting with "9" text field?

if yes,

=COUNTIF(Data!A2:A4121,A7)-COUNTIF(Data!F2:F4121,"9*")

may work.

Ha, too quick, I realized I posted this in the wrong section of the forums. One moment...

Karroog · #4 06-18-2009, 10:24 AM

Quote:

Originally Posted by NBVC

is the field starting with "9" text field?

if yes,

=COUNTIF(Data!A2:A4121,A7)-COUNTIF(Data!F2:F4121,"9*")

may work.

Changed the field to a text field and changed it instead of 4 question marks to a star, but no luck, still displaying the original count of all the medical stuff.

P.S. don't ban me, I misread this section of the forums :P

Karroog · #5 06-18-2009, 10:33 AM

If you want I can stop this thread and put it in its appropriate location :P

NBVC · #6 06-18-2009, 10:34 AM

Try then:

=SUMPRODUCT(--Data!A2:A4121=A7),--(LEFT(Data!F2:F4121)="9"))

P.S. I will move thread for you.

Karroog · #7 06-18-2009, 10:44 AM

Quote:

Originally Posted by NBVC

Try then:

=SUMPRODUCT(--Data!A2:A4121=A7),--(LEFT(Data!F2:F4121)="9"))

P.S. I will move thread for you.

Gave me a #Value! Error. I added an extra Parenthesis after SUMPRODUCT, excel shot my an error back. Are the double negative signs needed?

NBVC · #8 06-18-2009, 10:50 AM

Should've been:

=SUMPRODUCT(--(Data!A2:A4121=A7),--(LEFT(Data!F2:F4121)="9"))

If still no good.. please post sample workbook.

rwgrietveld · #9 06-18-2009, 10:54 AM

I always like the solution with SUMPRODUCT. I do see a small error in the formula:

Code:

=SUMPRODUCT(--(Data!A2:A4121=Data!A7),--(LEFT(Data!F2:F4121)="9"))

Karroog · #10 06-18-2009, 10:57 AM

coming up with 0 as the value now. Meeting in 2 minutes, going to upload the workbook when I get back.

Karroog · #11 06-18-2009, 11:09 AM

Nevermind, meeting canceled. Figures the meeting that only requires 5 seconds of input from myself gets canceled....

Took out a lot of the un-necessary info and put in specifically whats needed. To be clear:

The A range is the same, but switch F columns to C for this workbook.

The numbers inbetween the name and the Job number are there for a specific reason. 10,11,12 and all numbers above 30 are specific products. However, 30 itself is a mash of many types of products, so that is why I am trying to sort by name, not by number. And as you can tell I am not done categorizing product code 30 by name.

NBVC · #12 06-18-2009, 11:14 AM

It worked for me...

I get 38...

I put "Medical" in M1 to refer to it.. and formula in N1

Karroog · #13 06-18-2009, 11:16 AM

Quote:

Originally Posted by NBVC

It worked for me...

I get 38...

I put "Medical" in M1 to refer to it.. and formula in N1

That is screwed up....

Give me 5 minutes, I want to try it on my original excel spreadsheet

Karroog · #14 06-18-2009, 11:25 AM

Quote:

Originally Posted by Karroog

That is screwed up....

Give me 5 minutes, I want to try it on my original excel spreadsheet

Found it:

In the original spreadsheet there are a lot of spacing issues because I imported the excel sheet off of a data sheet from a program called Crystal Report. It has a tendency to mesh all the fields together to look good on a printout. The original function I had F as the area, but it was actually D. That's why it wasn't working.

And the 38 for medical, I just can't count this morning :P

Thanks again!

NBVC · #15 06-18-2009, 11:35 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Welcome to the Excel Forum

Forum Statistics