# Formula Return The Number Of Item For Each Manager

Jan 9, 2009i need a formula in Cell B2, that will look in raw data and then look for the team in criteria (named range) then return the number of item for each manager....

View 9 Repliesi need a formula in Cell B2, that will look in raw data and then look for the team in criteria (named range) then return the number of item for each manager....

View 9 RepliesADVERTISEMENT

I am trying to see if there is a way using count or another option, of using one formula to look in a column and then spit out how many of each item are there:

i.e: lets say it says a, b, c, d etc...

Can a formula go through and say you have 3 of a, 5 of b and so on

I understand the basic of count sum iserror etc, just not sure if excel can do what I am wanting or if i should use access.

Here I would like to get your valued guidance for the usage of "IF" formula. I am having two sheets one is new XL-1 and other is old XL-2. I want to have a item number from the old file corresponding to the same item descprition in the new file. I am attaching the snap shots of two files which is more explanatory than describing in words here.

View 2 Replies View RelatedI am using a vlookup and have a problem. I am assigning a category to an item number based on the first two characters of the item number. For example item number 60123 would equal scrap because of the first two characters of 60. But the item number can begin with either a number or letter. Here is the formula I am using that works for item numbers that begin with numbers:

=VLOOKUP(VALUE(LEFT(E2,2)),Sheet3!A:B,2,FALSE)

It works fine until I reach a item number that begins with a letter, then I get the dreaded #Value error. If I take the value out of the formula then it works for the letter based number items but not for the number based item numbers.

I am using Vlookup to compare our item number with the suppliers item number but the result is removing the decimal point on some but not all items.I have included a small sample but it concerns almost 10.000 items.

View 9 Replies View RelatedI'm trying to copy images based on an adjacent cell value using name manager but it does something strange to the image... On the attached example I want cell E5 to be the flag of the country in F5 by using an index/match formula in name manager but the flag seems to shrink and break up when the country changes and the original flag is changed.

View 2 Replies View RelatedI have a sheet in which in Coloum A I have manager names, then in Colum B I have list of Employees reporting to that manager, then in Column C, I have total login time of each employee which should be => 8 hours. In a new sheet, I want to get the count of employees next to the each manager's name who login time is less then 8 hours.

View 3 Replies View RelatedIf/when I have removed information from a row, can anyone tell me how to remove empty row but leave row numbers intact and following each other in numerical order.

View 5 Replies View RelatedI am using vlookup to return a number from sheet 3. I then need to use the number that vlookup returns in a formula on the worksheet, but since the cell containing the number it returns is actually a formula itself (=Sheet3!A5), the formula returns #value!. How do I get just a number/value that I can use in the next formula?

View 4 Replies View Relatedcompare one coloum with another and return a corresponding value in the adjacent cell. For example

I have 4 colums A,B,C,D. In "column A" I enter Products names (Example- Apple, Orange..)and in corresponding Coloumn B, I enter Product Codes (01 for Apple and 02 for Orange etc..) When I enter Apple in coloumn C for 10 rows (C1 thru C10), I need to get Code 01 in Column D in all rows (D1 thru D10)

I need to search from a multiple column and return its corresponding outcome . Make it simple,

Price Fruit Vegetable

5 Apple Tomato

12 Banana Carrot

29 Watermelon ginger

what i need is a formula where when i key in either the fruit name OR the Vege name, its price will shown on a cell.

I try with Vlookup..but as I am new and i failed when i try 3 x 3 range.

My objective is to take two different random counts of numbers written in column b, ranging from B9:B66. However I want to highlight the specific fields listed in the example below, and pull random numbers from those selected fields only. Then I want to pull another set of random numbers in the same column B9:B66, however using a different specific set of fields within column B; as shown in the second random formula string. Use the Press 9 feature to randomly select the two different numbers. What this attempt is trying accommplish is selecting one boy, one girl who have assigned numbers adjacent to their names(name field populated in column c).

What am I doing in correctly?

HTML Formula written to B70

=INDEX(B9:B66,RANDBETWEEN(1,COUNTA(B9,B13,B15,B16,B17,B20,B21,B23,B25,B31,B33,B34,B35,B37,B38,B39,B41,B42,B44,B45,B47,B50,B51,B52)),1)

Formula written to B72

=INDEX(B9:B66,RANDBETWEEN(1,COUNTA(B10,B11,B12,B14,B18,B19,B22,B24,B26,B27,B28,B29,B30,B32,B36,B40,B43,B46,B48,B49,B53,B54,B55,B56)),1)

I have the following table in a spreadsheet and want to return the number of rows where the Matched column is "F" and the Type column is "L".

Ref

Selection

Stock

Order

Matched

Type

[code].....

So in the example given the formula should return 1.

I have a list of product IDs in one col. In another 2, I have date and vendor that each items was sent. Note that each items can be sent several time to different ven for at different date. From this list I would like to build another list that shows me WHEN & WHERE each item was last sent.

I was able to find a formula to return a list of unique item for my product IDs, but get stuck on the next step.

I am looking to return a sequential list of items which match to an element of text that I have selected. An example will explain what I want better.

Item Selected Tom

Ref AmountTom C 32206 3683Tom B 36212 34464Frank 10552 43642Mary 13228 30534Tom A 30694 35798Mary B 9121 27489

So I want a Formula which will return the Reference for items in column A which contain the word Tom.So the Result I want is:Selected

Tom C 32206 3683Tom B 36212 34464Tom A 30694 35798

The formula I have to return an item containing Tom is:

=INDEX($A$5:$A$10,MATCH("*"&$B$2&"*",$A$5:$A$10,0))

But this only returns the first Tom and no other.The Formula I have to return sequential items with exact reference:

=INDEX($B$4:$B$11, SMALL(INDEX(($B$2=$A$4:$A$11)*(MATCH(ROW($A$4:$A$11), ROW($A$4:$A$11)))+($B$2$A$4:$A$11)*1048577, 0, 0), ROW($A1)))

This will (if I change the row reference at the end) return all Tom C's (If I change B2 to "Tom C") in sequential order but only the Tom C's and not the other Tom's (Tom B and Tom A). I have tried replacing the $B$2 with "*"&$B$2&"*" but that doesn't seem to work.

I want to get the value for the selected item from the combo box in excel. I don't know very much excel but I want do this forlmular.

See attachment. I want in this box real value…. If I choose livingroom I want to show value of livingroom = 20

I have a Training Matrix with "Main", "Leavers" and "Reports" sheets. (can email it but can't upload it here; it's 534kb and I can't make it smaller)

In the "Reports" sheet I need to fill "Site Procedural Training" - "Number of people trained in period".

The periods are :

25/12/11 - 31/03/12

01/04/12 - 30/06/12

01/07/12 - 29/09/12

30/09/12 - 29/12/12

The source of data will be the "Main" and "Leavers". The data are dates in these sheets; each date = 1

The formula needs to look in to "Main" and "Leavers" and if it finds a date which fits in the required period in "Reports" then return it as 1. If it finds 300 dates then returns 300.

I need a formula that will return number of aged breaks under each criteria in sheet 1 row 1 and each team based on the unique case number, so for example in sheet 2 I have my raw data starting in A1, i should get the below results in sheet1 ...

View 9 Replies View RelatedI have two data columns: the first is a list of the last 100 calendar dates, and the second is an ON/OFF (or 1/0 if you prefer) value corresponding to each date. If the last value in the second column list is ON, I want to find how many continuous days the value has been ON for. If the last value in the second column is OFF, I want it to return a zero. Note that in the 100 day span of data, the value may switch between ON and OFF several times, but I only want the continuous, uninterrupted ON days at the end of the date range.

View 8 Replies View RelatedI have a spreadsheet!

I have two sheets, one of which contains film names and the other contains our tag structure for our website (which is a list of tags, their keywords and the primary parent channel they live under).

What I am trying to do is search the film titles and if any of the words match either the tag name or keywords then return the relevant channel.

Example:

Sheet 1 - Films

How To Apply Bridal Makeup

How To Fight A Donkey

How To Write Excel Formulae

Sheet 2 - Tags

Channel / Tag / Keywords

Tech - Microsoft - windows vista xp word excel

Tech - Computers - internet pc

Tech - MP3 Players - iPod Zune

So, for film 3 on Sheet 1 it would recognise the word Excel in the keyword list and return the channel Tech.

I have the following formula which returns the number of asset breaks for each head of dept in sheet3 based on their teams in Sheet1 and then does a countif in >30_DAYS_ASSET in sheet2. In sheet 1 is a mapping table with range names "Teams2", "Teams" & "Managers". I now need the formula to also do a count on the unique numbers in sheet2 Column B, if there are duplicate numbers then that will be 1 asset break, so for example for for Joe plummer we can see there are duplicate case numbers so the formula in total should return 2.

=SUMPRODUCT(--ISNUMBER(MATCH(Teams2,INDEX(Teams,MATCH(C11,Managers,0),0),0)),

COUNTIF('>30_DAYS_ASSET'!$B:$B,Teams2))

Sheet1

AB2ManagersTeams3JOE BLOGGSAMP14JOE PLUMMEREQADMIN567Teams28SECADMIN9AMP1

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Sheet3

AB4Head of DeptASSETS5JOE BLOGGS286JOE PLUMMER3

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Sheet2

AB6SourceCase No.7SECADMIN4498618SECADMIN4498619SECADMIN44991310AMP144986211AMP144918012AMP1550935

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

I'm trying to create a formula in cell f13 of my attached spreadsheet "Sample 1" that will search the 2nd attached spreadsheet "Sample 2" and return the correct serial number based on both the matching PO # (located in cell E10 on Sample Sheet 1 and in Column 5 on Sample Sheet 2) and Product # (cell A13 on my Sample Sheet 1). My current formula is not returning the correct result and I'm not sure why.

View 4 Replies View RelatedI've in a sheet something like this:

A B

Number Item

2 Apples

1 Orange

2 PineApple

5 Orange

1 Apple

I need to do a code to get how many Apples Orange, PinneApple or somethig is in the range

For Example:

3 Apples

6 Orange

1 PinneApple

The items are variable. It's starts in the second row, and finish in the next blank row.

I am trying to number rows based on criteria. when the value changes add a 1. This is what I need to have happen

Apple - 1

Apple - 1

Apple - 1

Orange - 2

Orange - 2

Plum - 3

Plum - 3

I can get an if statement to number everything 1.

I have two workbooks, (Invoice.xls) and (Inventory.xls) both using sheet1. When I fill out an invoice, with lets say 10 items on sheet1 column B, I need a macro to find those items in workbook "Inventory" sheet1 column A. When the item is found, I need the qty I entered on the invoice workbook "Invoice" sheet1 column C (same row as the item number) to be subtracted from the current total for that item in workbook "Inventory" sheet1 column C. I would like to run the code from the workbook "Invoice" before I do a "Save As".

I have found other Vb code on this site that seems very close to what I need, but I just don't know enough to make the changes necessary.

One has the master data in it ( Item #Lot#/Cases per pallet/date ) = 30K rows

Second is the list of item #'s .

I need to find the MODE of cases/pallet by item number.

I am using a vlookup with a table that has data which uses a number which correlates to an item. A person types the number into a cell which triggers the vlookup. See example

35apple37orange40lemon

So..if someone enters let say number 36 into the look up cell..since 36 does not exist in the table the vlookup returns the value next to cell 37. Is there a way to use vlookup so that if a number it is looking up does not exist it wont return the next value? I would like to return an error message if the number being looked up does not exist.

attached is my spreadsheet. What I would like it to do is when someone marks a "N" or some other symbol in the box showing that supply not there; that excel copies the item #'s for the products listed and calculates up the prices for those items.

Thank you in advance for your help. If at all possible if it could calculate the order on another sheet such as sheet 3 that would be fantastic

I use the code below to enter a value from a list box in a cell on a workssheet. Is it possible to code VBA to enter a number for the position of the selection in the listbox to a cell in a worksheet rather that the actual value from the list box. For example if my list is:

Option1

Option2

Option3

And I click on Option2 in the list, I can sennd the value 2 to a cell on the worksheet rather that the value "Option2' from the list.

Private Sub ListBox1_Click()

Sheets("SA").Range("SA_Poistion_To_Archive_A_New").Value = ListBox1

End Sub

If I have 1,000 entries in a column is there an easy way to determine the number of times a certain item is in the columns. For example if field A1 is "qwerty" I wan to see how many times "qwerty" or field A1 appears in the entire column?

View 2 Replies View Related
Copyrights 2005-15 www.BigResource.com, All rights reserved