# Counting And Lookups (count The Number Of Team Members Listed Under That Supervisor)

Feb 22, 2010
I have roster that has 12 teams on it, each team has their own column with the supervisors name in the first row. I want to look up the supervisor and count the number of team members listed under that supervisor.

View 4 Replies
ADVERTISEMENT
Jan 5, 2014

I am using an excel where I want to send mail to my team members for their trainings pendings,

Few trainings are once in a year and few traings are monthly.

View 1 Replies
View Related
Oct 8, 2009

I have three companies in column A, In Column B I have a date of when an item was sent to them, in Column C I have the amount of days this item has been out with that company. Is there a formula that will count the number of days an item has been out with one of the companies per month, and find an average if more than 1 item has been sent per month. Two items have been sent to company A in Sep 09, one item has been there 10 days the other 20 days, the answer I would be looking for is 15.

View 4 Replies
View Related
Apr 10, 2014

I have a sumproduct formula, I am trying to divide the output by the number of members used in the calculation:

This is my data set

4/10/2014102.6214/10/201499.5144/8/2014106.144/8/2014105.4

4/9/2014102.3284/9/201499.3864/2/2014105.8734/7/2014103.6

4/8/2014102.0884/8/201498.7953/17/2014105.5864/3/2014103.95

my sumproduct function is adding up all items that fall on that date which is fine, but i would love to calculate an average so divide by the number of items that were used in the calculation, is that possible?

=SUMPRODUCT(--(A1:G9=A11),(B1:H9))

View 3 Replies
View Related
Dec 11, 2012

I am attempting to count a number of cells in one column so long as they fall within a certain date, which is located in another cell in the same column. I'm trying to account for a number of reports that happened within a certain week.

The formula I came up with was

=COUNTA(COUNTIFS(A_DATE,">="&B2,A_DATE,"<="&D2))

But this returns 1 more then it should no matter what. Even when all cells within A_DATE are empty. it returns 1.

I know the COUNTIFS portion works to count the cells between certain dates.

Is what i'm looking to do possible or am I suffering from Excel burnout and just can't see that logicaly it makes no sense?

View 2 Replies
View Related
Jul 11, 2006

On sheet1 I have a column with multiple names. I need a function that can

look up all those names from column B of sheet2, and then count the occurance

of a value in column H of sheet2. Here's the example:

Sheet1 (note that some rows are blank, and some names may appear >1 time):

Helen Back

Cyndi Lou Who

Cyndi Lou Who

Sam Spade

Sheet2 (names only appear once):

col Bcol H

Cyndi Lou WhoR2

Dan DangerR1

Dou LuvaR3

Hal BrookR2

Helen BackR1

Joe MamaR2

Lou DuvaR2

Mai TaiR2

Roberta FlackR3

Ron PopeilR2

Sam SpadeR2

Will NotR2

Yodan/a

I need a formula that can do lookup sheet1's names in col B of sheet2,

then give me a count of all values "<>R1" in col H. If names are repeated in

sheet1, I do want to include the multiple occurences in my count.

View 11 Replies
View Related
Jul 9, 2008

I have the following data in a number of cells and I would like to perform a count on match if a team has won, drawn or lost

Nottinghamshire beat Derbyshire by by 53 runs

Lancashire beat Yorkshire by 30 runs

Durham beat Leicestershire by 8 wickets

Midlands/West/Wales

Worcestershire v Warwickshire - No result (Match abandoned)

Gloucestershire beat Somerset by 8 wickets

View 9 Replies
View Related
Sep 10, 2013

The values in columns b:d range from 000-999. I need a formula that will count the number of digits in B:D that match the 3 values in H1 without counting a digit twice. The existing formula counts a digit twice, like the value in H4.

DIGIT HITSABCDEFGH10,1,2209/09/132192191222309/09/134824821441409/08/131191191112509/08/139799792550609/07/137177171551709/07/138668662000

Excel 2007Worksheet FormulasCellFormulaE2=B2&C2&D2F2=SUM(B2:D2)G2=MOD(F2,10)H2=SUMPRODUCT(--ISNUMBER(FIND(","&$B2:$D2&",",","&H$1&",")))E3=B3&C3&D3F3=SUM(B3:D3)G3=MOD(F3,10)H3=SUMPRODUCT(--ISNUMBER(FIND(","&$B3:$D3&",",","&H$1&",")))E4=B4&

[Code] ......

View 9 Replies
View Related
Oct 16, 2013

I have a set of data like below, I am trying to get a formula to look at a range i.e. May column and then select the highest number in that range but rather than returning the number returning the team name, is that possible? Not sure where to start...

Jan

Feb

Mar

Apr

May

Jun

Team 1

25087

23029

32991

21319

25271

25136.8

[Code] .........

View 4 Replies
View Related
May 10, 2008

I'm trying to build a IF formula in column F, to use one of five different Vlookups based on cell content of D2 on down. I can't seem to figure this one out..

View 8 Replies
View Related
Dec 26, 2006

How do I get a correct average of each supervisor from the data breakdown in columns f:g?

I was thinking an if statement like =if(a2:a10,"x" --then I am not sure how to say average of f:2:f10). Would that be on the right track?

*

ABCDEFG

1*****knowledgable*friendly*

2john*mike*dave*bill*lisa*5*4*

3john*mike***lisa*4*3*

4john***bill*lisa*3*2*

5*mike*dave*bill*lisa*2*4*

6john**dave*bill*lisa*1*4*

7*mike*dave**lisa*1*3*

8john*mike*dave*bill**2*5*

9john*mike*dave*bill**3*3*

10john*mike*dave**lisa*4*5*

View 9 Replies
View Related
Nov 21, 2007

Im trying to construct a nested Countif statement. I need to count the number of instances that "Project" appears in Column O AND "TS" in Column N. The range is in another in Sheet2. and the summary in Sheet 1 where I want to have the Countif(AND...??? statement Example Counif(Sheet 1 Column 0 contains "Project" AND if Column N Contains "TS"

View 2 Replies
View Related
Nov 22, 2007

I've tried everything I know (which isnt that much to be honest. lol). Ive tried the frequency formula but that doesn't work the way I want it - I think its probably the wrong formula to use. I've also tried a pivot table but they always vex me. If a pivot table IS the way to go, could someone talk me through it step by step? (*the wizard is just as confusing as doing it yourself I find) ....

View 9 Replies
View Related
Jul 10, 2014

I have a spreadsheet for tracking jobs. Most everything is based off of week # rather than date. I am trying to get the stats page of the workbook to tally the total number of late jobs per week.The current week is taken care of because there is a function that automatically displays on time yes or no and I just set it to count the yeses or nos.The problem I am having is for past weeks.

I tried- =COUNTIF(Table2[On-time],"No"+(CountIF(Table2[Week # Hidden],"<Weeknum(Now())" but that doesn't work. I also tried isolating the < like this. =COUNTIF(Table2[On-time],"No"+(CountIF(Table2[Week # Hidden],"<"Weeknum(Now()) and that did not work either.

------ UPDATE

In response to using CountIFS I have also tried-

=COUNTIFS(Table2[On-time],"NO",Table2[Due Week '# Hidden],"<Weeknum(Now())") this just returns a zero value even when I have a late job listed three weeks ago.

View 10 Replies
View Related
Nov 27, 2007

i need to put serial no. without considering duplicate value.. i have ' N ' of value somewhere data is repeating when i am giving serial no i want to ignore duplicate value ...example given ... please see attached file..

View 4 Replies
View Related
Apr 11, 2014

I have a worksheet with Names in Column A. Date when the person joined in Column B.

On a separate worksheet I want to sort members by the months and year they joined by having all the months in Columns.

View 9 Replies
View Related
Jul 3, 2009

I would like to assign listbox members to a public variable (quite the opposite of what you normally do). I receive the error message "type mismatch".

Public MemberLB as Variant

Public Sub ListBoxTest()

n = ListBox_target.ListCount

For iCnt = 1 To n

MemberLB(iCnt) = ListBox_target.List(iCnt - 1)

Next iCnt

End Sub

View 9 Replies
View Related
Oct 16, 2007

I have a column that contains numbers relating to processing days. I need formulas that I can put into a results table that will show me processing timescales. I need to count up the number of 0s, 1s, 2s, 3s....9s and any numbers that are 10 and above. The column also contains #Value! errors and any formulas I have tried won't work with the error code and are also counting blank cells as 0 whereas I only want to count the cells in which 0 was typed in.

View 9 Replies
View Related
Apr 27, 2014

I have a data that looks like:

1

a

data_1a

1

b

data_1b

1

c

data_1c

[code].....

Is there a way to automatically make all possible combinations of deduction between members of group 1 only then of group 2 only etc? So it would look like:

data_1a-data_1b

data_1a-data_1c

data_1b-data_1c

data_2a-data_2b

data_2a-data_2c

data_2b-data_2c

Combinations between letter indicators but not between number indicators. BTW groups are different in size starting from 2 till 8 members.

View 6 Replies
View Related
Apr 29, 2009

I am trying to make a workbook to track crew members daily time.

I would like to be able to track time by Tag # and by W/O # for daily hour totals.

View 15 Replies
View Related
Mar 13, 2009

How do I activate the little box in VBA that appears after e.g. '.' (which enables me more quickly to see if I'm writing the correct code)?

View 3 Replies
View Related
Apr 27, 2007

I have created a UDF which determines what the name of the day is.

Public Function Dagnaam(daydate As Date, Optional Language As String = "Dutch")

weeknl = Array("Maandag", "Dinsdag", "Woensdag", "Donderdag", "Vrijdag", "Zaterdag", "Zondag")

weeken = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday")

dayno = Application.WorksheetFunction.Weekday(daydate, 2) - 1

Select Case Language

Case "Dutch"

Dagnaam = weeknl(dayno)

Case "English"

Dagnaam = weeken(dayno)

End Select

End Function

What i would like is when the user uses this function in the worksheet the field Language holds a dropdown list with predefined languages.

View 6 Replies
View Related
Oct 19, 2007

I'm using the following formula to get a count of records that have a matching function name that is in B10.

=SUMPRODUCT(--('Oct-2007_FunctionSurvey'!$T$2:$T$1498=B10))

My Problem: There are duplicate entries in my recordset based on COl A (AppID).

How can I change the formula to only count the occurences of the B10 values based on a unique AppID (col A)?

View 9 Replies
View Related
Aug 7, 2013

I need to count the number of equal cells in col D beginning at the top of the column. The counted cells must begin with a text prefix of "Category:" without the quotes.

Some but not all of the cells in col D begin with a prefix of "Category:" without the quotes, followed by a word or words following the word "Category:" See examples below. All of the terms prefixed with "Category:" in col D are in alphabetical order. I need to count the number of identical cells in col D with the "Category:" prefix.

Examples of the contents of cells in col D with the "Category:" prefix are as follows:

Category: Adversity

Category: Answers

Category: Assurance

Category: Blessings

Category: Build

Category: Change

Category: Children

Category: Choices

Cells above and below cells with a prefix of "Category:" in col D are not adjacent.Cells above and below cells with a prefix of "Category:" in col D are separated by 3 to an undermined number of rows.

I need to count the number of equal cells in col D and insert the count in col A at the last equal term. For example, col A above would have 93, 1, 1, 5, 10, 8, 3, and 12 inserted into col A.

View 9 Replies
View Related
Aug 21, 2006

going down are stores a, b, c, d.... what i'm filing in across is the square feet of each store and what quartr or year each store came into place. so there will either be a 0 or a number Now, I want to be able to count the number of nhew stores each quarter. how do i create a formula that just recognizes it the first time there is a number and not a zero... because i will put the square feet in subsequent quarters after it opens so i can see yearly how many square feet the store had. then also, how can create a button on the page that will say quarterly numbers and a button that is annual. so that i can hide the quarterly columns and just see an annual spreadsheet... and for the quarterly button so i can hide the annuals and just see the quarters....

View 6 Replies
View Related
Jan 5, 2007

I need to know wich formula or VB method, I have to use to count the number of cases in a range of colomns and rows C4:G107, have the same number. For eaxample how many times do I have number 1 and so on...

View 9 Replies
View Related
May 23, 2006

I have a column for EE or SP and a column that has an amount of insurance

coverage like 10,000 etc.

I want to count the total number of EE have that level or type of coverage

My columns are like this:

C1 EE K1 10,000

CE SP K2 200,00

I have tried numerous formulas from the various discussions and I can't find

any that works.

View 11 Replies
View Related
Dec 23, 2006

How do I create a formula that will count the number of times in F7:H106,

where the number "0" occurred and had a value of "1" to occur

in the previous row in the range, if no number "1" in previous row

then ""

examples

if G22=0 and G21=1, then count 1

if G12=0 and G11=1, then count 1 more

View 9 Replies
View Related
Jan 24, 2014

I have column in my table that calculates time in hours between now and ticket open date (ticket open date in B1)

so, in C1 i have following formula:

=(now()-B1)*24

Results are in hours, such as 15, 25, 32, 65 etc

In pivot table, I would like to see how many tickets with values of less than 24 or more than 48, etc

My available columns are: Region, Ticket#, AgingOpenHr(this new column).

I tried it different ways, and I cannot get it Less Than and Greater Than work under Values, works if I select grater than or less than in Label, but label does not produce consistent numbers.

View 1 Replies
View Related
May 6, 2009

I have the following worksheet, which lists the pickup groups and members of each group from a phone system. Another worksheet lists all the exts and I want to have some sort of VLOOKUP, MATCH function that will look at the ext number and come back with the pickup group number.

I've attached a copy of the spreadsheet to show you what I'm talking about. So looking at the spreadsheet if I have an ext number of 8001 I want to return the value of 2 as ext 8001 is a member of pickup group 2. Also of an ext is not a member of any group, then I want to return nothing.

View 2 Replies
View Related