Count / Lookup

1100feet

New Member
Joined
Feb 24, 2010
Messages
13
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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

mvptomlinson

Well-known Member
Joined
Mar 10, 2008
Messages
2,638
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

New Member
Joined
Feb 24, 2010
Messages
13
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

New Member
Joined
Feb 24, 2010
Messages
13
The above formula doesn't appear to work with Excel 2003

Any more thoughts?
 

mvptomlinson

Well-known Member
Joined
Mar 10, 2008
Messages
2,638

ADVERTISEMENT

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

New Member
Joined
Feb 24, 2010
Messages
13
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

New Member
Joined
Feb 15, 2010
Messages
49

ADVERTISEMENT

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

New Member
Joined
Feb 24, 2010
Messages
13
This seems to work using a sum array formula.

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

Forum statistics

Threads
1,137,351
Messages
5,680,991
Members
419,948
Latest member
Sbakker1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top