COUNTIFS - Need it to count based on 2 data ranges

[reidos2800]

Hi All,

I'm having some trouble with COUNTIFS in excel 2007 on PC. I need to count names on a roster where the person is a specific trade and on a specific shift.

I have two ranges of data, both with different text in the ranges. The first range contains the words (Fitters, Apprentices, Electricians). The second range contains the letters (D, N, R).

Basically I need it to count the number of times the letter "D" appears in the first range, but it also needs to count the number of times the word "Fitter" occurs in the second range in the same sheet. SO I need all the instances where someone is both a "Fitter" is also a "D".

The formula I tried was:

=COUNTIFS(HY12:IE120, "D", A12:A118, "Fitters"

But this is giving me the #Value error.

I can count the individual ranges, but when I want it to count the number of people where they are both a "Fitter" and a "D" I get the error.

Hope this explains it well enough.

If someone can help me I'd really appreciate it.

Cheers
Alex

[FDibbins]

Hi and welcome to the forum

your ranges need to be the same size for all criteria ranges....
=COUNTIFS(HY12:IE120, "D", A12:A118, "Fitters"
either change both ranges to 120 OR to 118, then try again?

[Pete_UK]

XL Help for COUNTIFS includes this statement:

IMPORTANT: Each additional range must have the same number of rows and columns as the criteria_range1 argument. The ranges do not have to be adjacent to each other

.

In your case you have rows from 12 to 120 for criteria_range1 and from 12 to 118 for criteria_range2, and columns from HY to IE for range1 and for just column A for range2.

Hope this helps.

Pete

[reidos2800]

Thanks for your help and the quick replies guys, appreciate it.

I've updated the range to 120, but still no luck. I think the second part of Pete's comment sums up the problem.

As this is a weekly roster, I only want to include the HY to IE range and then have it relate back to the start of the sheet where I have the type of employee they are (Fitters, Apprentices and Electricians).

I need to know how many "Fitters" will be working on a particular day and how many of them are classified as employee type "D", which means day shift.

The HY to IE range has all the shift types such as D, N and R

Maybe I'm using the wrong formula for this.

Cheers
Alex

[daddylonglegs]

Try SUMPRODUCT like this

=SUMPRODUCT((HY12:IE120="D")*(A12:A120="Fitters"))

[Pete_UK]

You could adopt this approach:

=COUNTIFS(HY12:HY120, "D", A12:A120, "Fitters") + COUNTIFS(IA12:IA120, "D", A12:A120, "Fitters") + COUNTIFS(IB12:IB120, "D", A12:A120, "Fitters") + ...

and so on, but that seems to be crying out for simplification, maybe with an array formula (can't think of one at the moment).

If you post an example workbook then we could try out some other approaches for you based on SUMPRODUCT or array formulae.

Hope this helps.

Pete

EDIT: Ah, DDL to the rescue !! - Thanks.

[reidos2800]

Thanks Everyone for responding so quick!

@Daddylonglegs - Thanks for your help, the SUMPRODUCT formula you gave me seems to have done the trick!

@Pete_UK - Thanks for this, I'll run with the SUMPRODUCT formula.

I did a manual count and it appears to have worked.

Now the fun part in duplicating this for the entire roster and all work types!

Cheers guys

Alex

[Pete_UK]

Now the fun part in duplicating this for the entire roster and all work types!

Draw up a 3 x 3 table somewhere, using as headers (across) the words Fitters, Apprentices and Electricians, and labels for the rows of D, N and R, the use DDL's formula but with absolute references for the ranges and with cell references to the headers and labels, so you can then easily copy the formula across and down.

Hope this helps.

Pete

[reidos2800]

Thanks Pete,

Yep that helped. Have put the table in and am just linking it to that. Using the $ signs on the stuff I need to stay the same.

Thanks again all!

Cheers
Alex