Count / Lookup

[1100feet]

Hello,
Please be gentle, I'm new.

I'm working on a roster in excel.
It is setup as col A persons skill lvl (0,1,2 or 3)
col B persons name
col C - ?? days of the month

Now lets say we have rows 3 - 10 with persons 1-8
Persons 1-4 are working a day "d"
persons 5-8 are working a late "l"

I have a countif at the bottom counting the number of ppl on "d"
That part is working fine.

Now the problem
Under my countif telling me that there are 4 ppl on day I want to know how many OF THOSE PPL are a skill lvl >2 (listed in col A to the left of the name).

Can this be done with some sort of vlookup and/or a count of some sort?

Hope this makes sense.
I'm hoping to avoid VBA and want to achieve this via formula.

Thanks in advance.

Matt.

 

[mvptomlinson]

1. You don't tell us what version of Excel you're using.
2. You don't tell us which column houses the "D" or "L".

Assuming you're using 2007, and assuming the D's or L's appear in column D, use:

=COUNTIFS(D3:D10,"d",A3:A10,">2")

 

[1100feet]

Apologies,
Excel 2003

I will be repeating this for each col c - ??
for each day of the month.

Thanks mvptomlinson. I'll let you know how it goes.

 

[1100feet]

The above formula doesn't appear to work with Excel 2003

Any more thoughts?

 

[mvptomlinson]

See my signature for file posting options, or download Excel Jeanie (link somewhere on forum or Google). Still don't know what column C is for, and where you're counting d's and l's.

 

[1100feet]

Unfortunalty as I'm at work I can't access file sharing sites.
I'll keep hunting and if need be upload some stuff from home tomorrow.

 

[Jimatdks]

One not so eloquent way of doing this would be to add another column with the formula:

=IF(C1="d",IF(A1>2,1,0),0)

**This would be added to the first row of data you have...it is fine as is if your first row of data is in row 1, but if it is in row 2, the "C1" and "A1" in the above formula would change to "C2" and "A2" and so on and so forth...

The formula asks if cell C1 has a value of "d" and if it does, the formula then asks if the cell A1 has a value greater than 2. If both of these are true, then the formula outputs a value of 1. If one or both of these are false, then the formula outputs a value of 0.

You could then simply sum column D (or wherever you are copying the above formula) in the cell where you want to display the number of people with "d" and skill level greater than 2.

If you dont want the 1's and 0's to show up in your worksheet, simply right click column D (or again, wherever you are copying the above formula) and select "Hide".

Hope this helps. I am sure someone out there is smarter than me and will give you a more eloquent answer...

-Jim

 

[1100feet]

This seems to work using a sum array formula.

{=SUM(IF(D1:D4="l",IF(A1:A4>1,1,0),0))}

 

[mvptomlinson]

A non-array version might be:

=SUMPRODUCT(--(D1:D4="l"),--(A1:A4>1))

 

[lumite]

1