Sum By Text & Cell Criteria
Feb 14, 2008
I have a working list of partners with a quantity received & category received in.
Partners receive in multiple categories, so I need to be able to breakout partner quantities received by category on separate worksheets.
In the working list I have partner names in column B, category in column D & quantity in column E. I then have separate worksheets for each category with partners listed. I would like to be able to reference the partner name in these separate category sheets so I can just drag the multiple criteria quantity reference formula down. I tried this formula below, following the multiple criteria help section but I cannot get it to work:
= SUMPRODUCT(('working list'!D:D="CC Lead")*('working list'!B:B=A2),'working list'!E:E)
View 3 Replies
ADVERTISEMENT
Feb 28, 2007
Greetings, I have a particular column containing data similar to:
X24A
X15B
G15A
X23S
I have sorted the data to have all the X marks together, and now I want to run a counter so i can select the data and delete. Following is a sample of the code that is NOT working:
'Delete X Piece Mark Rows
Range("L2").Select
counter = 0
ActiveCell.Select
Do While ActiveCell = "X" & "*" ' This is the line that crapping out
If ActiveCell = "X" & "*" Then counter = counter + 1
ActiveCell.Offset(1, 0).Select
Loop
Range("A2").Select
Range("A2", "L" & counter + 1).Select
Selection.Delete Shift:=xlUp
Range("A1").Select
View 2 Replies
View Related
Nov 8, 2013
The above formula is not working as in A9, I have a formula although there is no physical text in the cell as the criteria applied is false. However, B9 still appears.
=if(istext(a9),b9,"")
View 3 Replies
View Related
Dec 19, 2011
Is there any method to speed up a for each loop to count text in a cell with multiple criterias in a single column. This is on example:
Code:
For each rr in r
If rr = "a" And rr.Font.Strikethrough = False Or rr = "B" _
And rr.Font.Strikethrough = False Then
a = a + 1
end if
next
View 1 Replies
View Related
May 8, 2009
I'm racking my brains as to how I can structure a formula to conditionally rank a value in an array against only those values in the array whose corresponding criteria cell includes a specific letter.
So for example I have a list of 12 values, say 126; 239; 0; 171; 162; 157; 130; 199; 122; 153; 0; 15.
Each of those values corresponds to a heading, say: CDE; DFE; FGE; DFE; ERD; DEA; BDF; DFB; CDE; CEF; CAB; FAB. As you will note some of the headings may or may not be the same and may or may not include the same letters in different orders.
How can I write a formula that ranks in ascending order a given value drawn from the above list (which will be in another cell but which in this case is, let's say, the first value: 126) only against those values whose heading includes a specific character, for example the character C (the character in question will vary and be defined in a specific cell).
As an added complication I need the ranking calculation to exclude any zero values. So in the above example what the formula needs to do is rank the value 126 against a sub-set of the whole array comprising only the values 126; 122; 153; 0.
The answer I need is 2 because, discounting the zero value, 126 is the second highest value.
View 9 Replies
View Related
Jul 19, 2014
I need to analyse more than 4500 product of my company. I have data in more then 20 separate excel worksheets, I need a function or a vba code to return the text against my product names.
I my main file i have the names of the products, sales and purchase data and analysis. In other files I have the categories list against those products. In the main file the products are according to brand and categories have different products from different brands grouped together. I want a function something similar to sumif but for text.
Example
What I need
Art no. Product Name Brand Group Category Subcategory
y-123 abc lux hair care shampoo hair shine shampoo
A-123 bca loreal skin care anti acne anto acne soap
Main file
Art no. Product Name Brand Group Category Subcategory Q1 sales Q2 sales Q3 sales
y-123 abc lux 120 110 105
A-123 file with categories against respective products.xlsxmain file what i have.xlsxmain file.xlsxwhat i need.xlsx
Category files
Art no. Product Name Brand Group Category Subcategory
y-123 abc lux hair care shampoo hair shine shampoo
View 1 Replies
View Related
Jun 20, 2012
Right now I have this working to color a row green if Column A contains the word "Open", I just need to add to this to say Column a="Open' and Column H="Red"
How do I do this? This is what I current have
=$A6="Open" (I need to add that if $H6="Red" as well)
How do I fix the syntax formula that will achieve this.
View 3 Replies
View Related
Jan 12, 2008
I have a search macro that runs from a command button on Spread sheet Called "find". This currently takes what ever the user types in cell A1 and then searches another sheet called "data" for any instances of what ever the user entered in Cell A1. I want to have a text box on sheet "find" and use that instead of Cell A1. (To keep this posting as short as possible I wont explain why I need to do this). I cant get the syntax right to refer to the text box - can someone point me in the right direction please.
View 7 Replies
View Related
Jul 5, 2012
Trying to create a button that, once clicked performs the following task:
Checks whether the text in a cell in the column X2:X40 matches the text in a cell in the Range A2:U14.
If it does, the formatting of the cell in X2:X40 (eg background) must change to match that of the corresponding cell in A2:U14.
Only 1 cell in the range will match 1 cell in the column
For example:
Before click:
Cell A9 has "John" written in it and a yellow background
Cell X4 has "John" written in it and a clear background
During click:
matches these cells
changes X4 so that it also has a yellow background
After click:
Cell A9 has "John" written in it and a yellow background
Cell X4 has "John" written in it and a yellow background
View 3 Replies
View Related
Jan 28, 2014
I have 2 tabs in a 2013 workbook. Inventory Receipts and lookups. One of the Data verification lookups I have is a drop down list in each cell in Column B (eg: Cat, Dog, Mouse) In the lookups tab I have another cell range containing the sounds (eg: Meow, Bark, Squeak).
What I am trying to acheive is, if B2 contains Cat, then return Meow in B3, if B2 contains Dog then return bark in b3 and if B2 contains Mouse return squeak in B3. Ideally I would like the formula to return the text from my lookup sheet (eg: 'Lookups'!C2,'Lookups'!C3,'Lookups'!C4). So depending on which option they choose from my animal drop down list .. the correct noise would automatically fill in.
View 9 Replies
View Related
Jan 24, 2013
I have four cells that contain text. All have connected check boxes with TRUE FALSE.
I need to be able to select anyone one of these cells with a check box, and have it's text appear in one separate cell eg: A1.
I have no issue connecting check boxes etc. I have no issue reproducing the text from any of these cells into multiple cells with a check box. But they have to be selectable and reproducing in one cell only (eg"A1").
View 1 Replies
View Related
Apr 5, 2013
I'm trying to build a formula on the matching text as below, im getting an value error, however when i press the FX key it shows the correct result.
Column A
Column B
Column C
Column D
[Code] ....
( I would like to match the below two criteria with the Row 1 and Row 2)
Co1
#VALUE!
Latest Forecast
Formula-->
IF(MATCH(A6&A7,B1:I1&B2:I2,0),"Ok","Not ok")
View 1 Replies
View Related
Mar 18, 2009
Trying to sum a range based on the criteria of the 1st position within each cell in the range. Cells have data like "A-8", "A-4", for a total of 12 for "A". This is the formula I have that is not working.
=SUMIF(left(C4:P4,1),"A",RIGHT(C4:P4,1))
View 6 Replies
View Related
May 29, 2007
to run a macro or any other methods to repeat the same head rows in my database as followings:
Let's say the main head row is in row 1.
Item names such as Item A; Item B; Item C ....in column A
What I want is to insert the same head row after the list of each item.
Item name (head row)
Book A
Book A
Book A
Item name (head row to be repeated)
Book B
Book B
Item name (head row to be repeated)
Book C
Book C
View 9 Replies
View Related
Sep 25, 2007
I have used the following Formula to count the number of times that ABC occurs in red text within the listed range, and it works fine
=PERSONAL.XLS!CountByColorText($G$4:$R$211,3,TRUE,"ABC")
I now want to count the number of times that (F) occurs in red text within the listed range, the difference being that (F) is always only the last three digits in the cell eg ABC (F)
I have tried this formula =PERSONAL.XLS!CountByColorText($G$4:$R$211,3,TRUE,"*(F)*")and get 0, which is incorrect
View 12 Replies
View Related
Feb 19, 2010
Im attempting to add number of dates within a column (B). I have 2 criteria: Today and person "William". Im using the following and return results is "0"
A: Name
B: Date
C: Type
=SUMPRODUCT(--(Data!$B$2:$B$3100=TODAY()),--(Data!$A$2:$A$3100="William"),--(Data!$B$2:$B$3100))
Once I get this working I would like to adjust for last 30days and look at Type (C) = New
View 5 Replies
View Related
Jun 2, 2009
Example: I have 2 sheets, a pivot and a data sheet. When selecting a different option on the pivot i want information returned from the data sheet (which is explanations of the information contained in the pivot) I need to add 2 criteria points.
View 5 Replies
View Related
Jan 26, 2010
I'm trying to create a formula that will allow me to pull test from a list (auto populate if possible). Essentially you will see on the second tab, a list of projects "Cali" for example. I'm trying to find a formula that will allow me to show the Customer and Life Cycle on the first Tab. If possible the Project Name too.
Essentially I want to be able to have all the data inputed into Tab 2 and let Tab 1 condense it into the designated fields. So basically what will allow me to see all of the "Cali" projects, and from that generate the Customer and Life Cycle (and Project if possible) on Tab 1. Keep in mind this does need to be automatic updating, so that as we input more information on Tab 2, it will automatically kick into Tab 1.
View 2 Replies
View Related
Jan 25, 2013
I have a drop down list called Status including Testable and Not Testable
A1 = Testable
A2 = Testable
A3 = Not Testable
A4 = =COUNTIF(A1:A3,"Testable")
Gives me 0... i don't get it. Should show 2 no?
View 9 Replies
View Related
May 3, 2014
Let's say I have a simple table like below:
Region
Date
Qty
N. America
23/4/2014
10
EMEA
24/4/2014
50
EMEA
25/5/2014
20
I want to sum up all EMEA quantities that are in the month of April.
I've tried this:
SUMIFS(C:C,A:A,"=EMEA",MONTH(B:B),"=4")
I've also tried:
{=SUMPRODUCT(IF(A:A="EMEA",1,0)*IF(MONTH(B:B)=4,1,0)*C:C)}
Both do not work.
Also as the data grows each month, I would want to refer to the entire column in my formula, e.g. 'Region' as A:A (rather than A2:A4).
View 4 Replies
View Related
Sep 22, 2006
how to do a weighted average with a text criteria. I have 3 columns and many rows. The first column has either "buy" or "sell". The thing is I need to find the weighted average based on the product of the other 2 columns for buy and sell separately e.g.:...............
View 5 Replies
View Related
Aug 17, 2007
Greetings respected EXCEL professionals. I am working an a project and I need your assistance. Kindly refer to the attached text file - which is a download of a BANK STATEMENTwhich came as text file. I want Excel to gather the information in the text file and put it in the cells. I then want to count the amount of all instances of certain descriptions.
For example, Excel would go down the description column and detect all instance of "EOD" (and ignore the varying string behind it) and put the total next to a cell titled simply "EOD". It would do similar detecting, calculating, and posting for the other descriptions ("DEPOSIT", "CASH", etc.) - again, Excel would only pay attention to the first few words of the description and just ignore any variables that occur in the same category.
I AM A NOVICE. I tried pasting the text file onto the spreadsheet with not too good results. But even when I tried to just have Excel count all occurances of "EOD" using the SUMPRODUCT function, I got a "VALUE!" error - I realized this is because there are texts such as "Amount" in the column containing the values.
View 6 Replies
View Related
Sep 4, 2007
I am trying to get the sum of some cells (integer varies in column G), but comparing one column content (exact) and dates in a different column.
I tried the following:
=SUM((Tony!G30:G44)*(Tony!D30:D44="Crazy")*(MONTH(Tony!E30:E44)=7))
Column E would contain a date, such as 01-07-07 or 1st July 2007.
In the D Column, keywords such as "Crazy" are concise and standard. However regarding dates, am I better off finding a formula that looks for cell content (Contains "july", as opposed to ="July"), or using a month function (but getting it to work)? How can I do this?
View 3 Replies
View Related
Sep 27, 2007
I tried to modify a sumproduct formula given previously by Kris which is to sum base on strings, to solve another problem but with no luck. My attempt without success is in BLUE column whereas the correct answers should be that in the RED column. Basically my problem is that if a condition is true, I want to concatenate the values of 4 cells with a space in between them.
View 4 Replies
View Related
Oct 18, 2007
I am looking to create an Excel sheet that can manage the times of access of a certain location. The information is set in a way that there are several rows, and the rows for the time I am keeping track of are in the Row "I". I need to get a count of how many people accessed the location each hour.
I have managed to do this using the following formula:
=COUNTIF(I:I,">=12:00:00")-COUNTIF(I:I,">13:00:00")
This gives me the Count for 12:00pm to 12:59pm. However, COUNTIF can only handle one variable, and I need to put a second variable in it. In Row B, there are several different states. For the most part, they are numbers, but occasionally, there are two separate occurances that I am not supposed to count with the others. I do have to count them separately, however:............
View 3 Replies
View Related
May 21, 2008
I've searched for the answer to this with no avail anywhere yet so beginning to wonder if it's possible. Basically, I want a text box to invoke a criteria in an Autofilter. I can do it in a way where if you put the exact name in, it will bring up that but I want to invoke "contains" in the criteria instead of "equals". It's normally done with "*" surrounding the criteria, but if you do it around the textbox, it won't look there.
Sheets("Sheet2").Select
Selection.AutoFilter Field:=4, Criteria1:=TextBox1, Operator:=xlAnd
Anyone know how to get TextBox1 to filter all products containing whats in textbox1 and not equalling it? I found this thread: Changing default settings of Autofilter...change from "equals" to "contains"
View 2 Replies
View Related
Apr 24, 2014
IN column J(on sheet 1) i want it to return text (OB) if Sheet 1 column A1 equals Sheet2 Columns A1:A500. And if Sheet 1 column A1 do not equal Sheet2 Columns A1:A500 return text(IB).
View 1 Replies
View Related
Aug 24, 2009
I am trying to figure out how to have some VBA look down Column 1 and where the Cell's string value = "GRANDTOTAL", to DELETE that ROW and ALL ROWS below.
Can anyone help me out in writing the Visual Basic code?
View 8 Replies
View Related
Jul 9, 2009
I have 5 cells with drop down validation lists for the different types of window that we offer. From this list I will select the options and then input some dimensions in cell dimension fields. I then have a cell that gathers the text string from the 5 drop down cells.
My aim is to be able to calculate glass sizes from the text string and the dimensions. Each of the possible text strings will require different calculations and will define the sizes that I need to order. I know the equations that I will need for each of the options but cannot think of how to make them dependable on the text string cell.
I am looking for some way of using IF function to say if the text string returns as certain text then there is a certain calculation to calculate from the dimensions for the glass sizes that I need. I dont know if I've explained it too well nor even if its possible but if it is possible I will find a way as I have done some pretty exceiting things with excel in the past.
View 11 Replies
View Related
Feb 22, 2012
I want to count cells that contain specific text but multiple criteria. For example in range B4:B12 I want to count the cells that contain the word 'daily' AND the word 'weekly'. Each cell in the range contains one word.
View 5 Replies
View Related
