SUMIF Using "Combo Box"
Dec 11, 2009
I have the following:
1) A summary worksheet that is using a combo-box to pull Country (US, Canada, Latin America, etc.) and Status (Open, Closed, Pipeline) information.
2) The combo box criteria are pulling Program Type (Program A, Program B, Program C) and Totals by Quarter-Yr based off the Country and Status as per the Combo-box selection.
3) A database of several thousand records hosted in another worksheet but in the same file - worksheet name is "ALL".
4) I have the database worksheet concating the Country, Status, Program Type, and Quarter so the formula used for #2 above looks something like this:
=SUMIF(ALL!$AO:$AO,$B$687&$AA31&AT$30&$B$688,ALL!$AT:$AT)
Where:
ALL! equals databased worksheet
AO = Concate of "Country&Program&Quarter-Yr&Status"
$B$687 = Combo-box name "Country"
$AA31 = Program A
AT$30 = Qtr-Yr
$B$688 = Combo-box name "Status"
This is working fine if I want to single out each Country but how do I include all...my issue is how I am concating the information in the "ALL" worksheet.
I was thinking a sumif with two conditions might work. Where first if the combo-box pulls up a country then grab that count otherwise grab count for all.
View 9 Replies
ADVERTISEMENT
Jul 6, 2006
I would like to write a function that enhances conditional formatting capabilities in this way: suppose that cell(4,5) contains a number that can be 0 to 5
in the adiacent cell(4,6) i want to put a function that:
1) write "NO DATA", "HIGH", "GOOD", "MODERATE", "POOR", "BAD" depending on that value
2)Choose color font depending on value
3)Choose color background depending on value
I wrote this piece of
Public Function StatusResponse(AdiacentCell As Range) As String
Dim thisStatus As String
Dim ThisFontColor As Integer
Dim ThisbkColor As Integer
Select Case AdiacentCell.Cells(1, 1)
Case Is = 0
ThisbkColor = 2
ThisFontColor = 1
StatusResponse = "NO DATA"............
View 2 Replies
View Related
Jun 4, 2009
I am very comfortable with SUMIF, but need to search a range any any cell containing words or phrase "20%" then need to sum those corresponding cells only.
I.e., something like: SUMIF(A1:A10,..."20%",C1:C10) .... only sum all C cells where an A cell contains any combination of words with "20%" in them.
View 2 Replies
View Related
Dec 27, 2009
I have a spreadsheet(see attached) that I am needing to find the amount spent based a a "tag" I have created. If you bring up the spreadsheet and look at the dashboard sheet, I am wanting to find the total tag amount based off what is listed in the paychesk sheet. For tests I have used this formula and have put it in B6 on the dashboard sheet.
=SUMIF('PayCheck - DEC-09-B'!$F$2:$F$1000,A20,'PayCheck - DEC-09-B'!F$2:$G$1000)
But the "bank amount" field on the paycheck sheet is a negative number the value of course coming back is not right. How do I fix this?
View 2 Replies
View Related
Oct 2, 2008
I have inserted the sheet. As you can see sheet1 has some Products codes in A, Products in B, Layer Quantity in C, In G and H I have Sales and Stock and in I is the order quantity. I would like for I the order quantity to automatically do the order when the sales and stock are shown in G + H. I need this to be based on the Layer Quantity in C.
So ( I5 ) would be 18 because I have 20 left and sold 17, the layer is 18 and 18 would cover the sales. I 14 would be 90 because 90 + the stock of 35 is 125 double the sales going up in 15s whick is the layer quantity. I need the Order quantity and the stock left to be at least double the sales but go in layer quantity
View 4 Replies
View Related
Jul 23, 2008
I'm trying to sum the values in a column if 2 different conditions (in 2 other columns) are true (so I'm evaluating 3 columns total).
For example, if I had a list of the prices of all the cars available at a dealership (each car listed on a different row), and I wanted to find the total cost of all toyota celicas listed, how would I do this?
Basically, I'm trying to create a formula that says "if the value in the make column = toyota AND the value in the model column = celica, then sum the related values in the price column."
View 9 Replies
View Related
Jan 14, 2014
Basically i have a data compilation of the sales for each sales agent for each month. I'm trying to create a simple, controlled table where the user can just choose which Employee(List Box) and the Month(List Box) and it will display their TOTAL Sales, the catch is TOTAL sales will be the sum of sales from previous months up till the chosen month.
Example below.
How do I go about inputting the function in the TOTAL cell?
JanFebMarAprMayJun
Kelly 3 4 2 1 6 7
Sha 1 3 2 4 2 6
Agus 8 6 3 6 0 9
EmployeeKelly
MonthFeb
TOTAL:7
View 8 Replies
View Related
Dec 5, 2008
I have a book of business spreadsheet which contains client name, policy number, insurer, coverage, revenue, etc. I need to separate coverages by whether they are fee based or commission based. If I auto filter the spreadsheet, I can ask for coverages which "contain" specific words, but I don't know how to do that in a sum if formula.
The object is to create a pie chart out of the results.
View 9 Replies
View Related
Mar 22, 2009
I can't seem to find out how to use wildcards like "?" and "*" in the VBA- Replace("string","s*r","k") which should give "king"
View 3 Replies
View Related
Feb 10, 2009
In column A, I have the following values:
build
chil rat
datcen
mang
nextg
ost rat
omp
sco rat
In column B, I need the formula so it shows values that if column A = "build", "nextg" or "datcen", then the value in column B is "nextg" and if column A = "chil rat", "ost rat", "sco rat", then the value in column B is "mig". For everything else, it should be blank.
View 5 Replies
View Related
Jul 19, 2007
I have two worksheets, one with "supplier names", "order numbers" and "delay in days" as columns. The other one with "supplier names" and "average delays" as columns.
It is the column "average delays" that I have issues with, I need Excel to search the "supplier names" column in the "delays" sheet and identify every specific supplier name, connect the delay in days for that order and calculate the average delay in the right cell of the column "average delays" in the sheet "delay statistics".
I have tried the help files and to search this forum but I have found nothing. I have also with my knowledge tried a few different ways using the IF function but nothing so far.
This is something I started doing but it is of course far away from any truth.
=IF(;Delays!B1:B200="Greber")..............
This is the "delays" sheet where I want to find my info. Nothing in the delay column stands for "no delay"...Kinda obvious but you know... So even the "nothing" needs to be included in the calculation..
View 9 Replies
View Related
Apr 16, 2009
tell me which operator works in VBA the way the OR operator works in functions? I want to write an IF statement that will evaluate whether a variable is equal to any of the three punctuation marks ".", "?", or "!". But I'm not sure how to code it. I attempted the following:
View 2 Replies
View Related
Jul 10, 2009
In my spreadsheet below I want to be able to enter a sales number for January, the value of cell F2.
I want cells F3 thru F12 to automatically calculate according to the "Growth Per Month" value in cell H1.
Example: If January sales are 20,000, then February should calculate to 21,000 (january * 105%).
Excel Jeanie HTMLSheet1
E F G H 1 2010 Per Month Sales XXX 5% 2 XXX XXX 3 February $ 10,000 4 March $ 5,000 XXX 5 April $ 2,500 6 May $ 1,250 7 June $ 625 8 July $ 313 9 XXX $ 156 10 September $ 78 XXX 11 October XXX 12 November $ 20 XXX 13 December $ 10 14 $ 39,990
Spreadsheet Formulas Cell Formula F3 =F2*10*H1 F4 =F3*10*H1 F5 =F4*10*H1 F6 =F5*10*H1 F7 =F6*10*H1 F8 =F7*10*H1 F9 =F8*10*H1 F10 =F9*10*H1 F11 =F10*10*H1 F12 =F11*10*H1 F13 =F12*10*H1 F14 =SUM(F2:F13)
View 9 Replies
View Related
Dec 20, 2008
As shown in the below image. I have some data from A13 to I 13. Currently if I put the cursor on A13 and press DELETE button then all the formatting from A13 to I13 goes off. But the data still remain there.
Is it possible then when I press DELETE the content of A13 then all the data from B13 to I13 should also get deleted ?
Excel Jeanie HTMLSheet2 *ABCDEFGHI13Key FieldEmp 11 2 4 8 5 3 7 Excel tables to the web >> Excel Jeanie HTML 4
View 9 Replies
View Related
Apr 29, 2009
i just tried the below formulae but its not working. if Currency in col A and Currency col B is same it should be "Matched" if not same the "CCY Mismatch" and if "#N/A Sec" then "Security Not Found"
View 3 Replies
View Related
Feb 24, 2009
I need to prevent users from entering several rows of data seperated by a "/" or a "," or a blank space ""
We use unique identifiers (around 500,000 of them) so I cant really use a drop down box to populate and then make the user select.....
If a user populates a cell with "10005486 / 10045446" or "10005486,10045446" I want to highlight a cell red and then count the instances of red cells on another tab so I can track "errors"
I was intending to use conditional formatting when a "/" or "," or " " is used within a cell. If a user makes this error, I should probably include a msgbox saying why the entry they have made is invalid also....
View 9 Replies
View Related
Oct 22, 2009
I'm having issues with sumproduct. I can't seem to get the right info that I need. Attached is the file I'm working on. Problem: I need to get the number of people "Occupied", "Partly Occupied" and "Available" on a Monday, Tuesday etc of the current week. "Occupied" means an employee has more than 2 tasks (based on New and Active-To-Date status). "Partly Occupied" means an employee is working on 2 tasks.
"Available" means an employee has NO task at all.
View 2 Replies
View Related
Nov 20, 2009
I have attached an example workbook to this message, with the sensitive data removed. I am trying to vlookup the "cube" for the product number, into the PO worksheet, IF the vendor numbers match.
Example:
On row 2 of the "PO" worksheet, part number AC1000110, should have a cube of 2.5 for vendor # 11170. I'm trying to match the "Vendor" on the "PO" worksheet with the "Vendor" on the "Cube File" worksheet, and then return the corresponding "Cube", (in column F of the "Cube File"), in cell U2 of the "PO" sheet. So, what formula needs to be entered in cell U2?
View 2 Replies
View Related
Feb 14, 2009
I am having problems referrring to a range using .Cells
If I try to use the remarked code (red text), it crashes with VBA "400" error for which I cannot find any explanation. It also crashes with same "400" message if I use a cells reference to a numeric column instead of "y").
The code works using the black it loops a column in Sheet("Holidays") and loops a row in Sheet("Schedule"), then color fills 2 ranges in columns in Sheet("Schedule") when the values in 2 cells are equal.
I have attached a scaled down version of my Workbook with this code.
View 2 Replies
View Related
Dec 2, 2009
I need "PC" to show up if I type a sentence containing "Called Parent", "Called Dad", or "Called Mon". Here is what I'm trying.
View 2 Replies
View Related
Mar 13, 2007
I have a procedure that processes data in a spreadsheet. It analyzes data in 365 sheets ( named "1" through "365") and creates a table/report with the results. When I run this on a scaled down version of my workbook (5 instead of 365 worksheets), the result is almost instantaneous. When this is on my normal workbook, the initial processing is about 1 second per day on Day 1, and the last day it speeds up to almost be instantaneous. It is not a linear relationship between the processing time (still working to get more exact timing information). Also, even if I limit my processing to 5 or 10 sheets, Days 1-10 are always slow and days 350-365 are always fast. The code being run is in the structure as shown below:
numSheets = 365
For counter = 1 To numSheets
x = CalculateSomething(counter)
Next
Private Function calculateSomething(counter As Integer) As Integer
Dim strCounter As String
strCounter = counter
With worksheets(strCounter)
For i = 0 To someNumber
For j = 0 To someDifferentNumber
'Data analyzed on worksheet
Next
Next
End With
End Function
The functions obviously aren't copied and pasted/functional, but the relevant efficiency stuff should be there.
View 5 Replies
View Related
Jan 24, 2009
I have a file that uses dates, when a cell is in date the cell is "GREEN" but when the cell is out of date after 3 years it turns "RED". I want to add another formular so that when it gets to 2 years & 11 months the cell will go "YELLOW" for 1 month only before it turns "RED".
View 3 Replies
View Related
May 12, 2009
I have the folowing criteria :
1) If “Completed" , then highlight as green
2) If “Pending” , then highlight as red
3) If “In Progress” , then highlight as blue
4) If “X Required” , then highlight as green
I need to include in the third conditional formatting box that if the cell populates "Completed" or "X Required", then highlight as green. Is there a formula I can use in the conditional formatting box ? I am using Excel 2000.
View 2 Replies
View Related
Jun 14, 2008
I want to replace all defined range names in the sheet that start with "Street" to "Road"
For example I have 50 defined names in the sheet as such "Street-01", "Street-02"... all through "Street-50"
I want to change them all in vba to Road-01", "Road-02" etc.
View 12 Replies
View Related
Nov 4, 2008
how to change the default output values "FALSE" and "TRUE" for the AND() function ? Let say I want to change them to "FAIL" "PASS".
View 2 Replies
View Related
Aug 30, 2009
i have a table in which some value is Equal to 0. Now i want a custom format which Displayed "-"(Dashes without quotes) insted of "0" (without quotes)
View 4 Replies
View Related
Nov 15, 2008
On the sheets where 'present' needs to be checked, after linking the checkbox to the cell it now says "TRUE" or "FALSE". How do I get rid of that? There is a formula that this affects also (just FYI).
View 3 Replies
View Related
Aug 26, 2009
I use the following code to get pivot table data source throught ODBC connect to CSV. However, when I chose hide item in "Page" fields, it showed "all" instead of "multiple items".
View 3 Replies
View Related
Jan 14, 2010
I can not figure out why the ChDir command doesn't change to "C:" as programmed, instead it defaults to (I'm guessing last active folder) on my "D" drive (Which is "D:Test")? My goal is to check if "PPG" exists in "C:MEASURE-6000", if not use "C:" as a default.
View 2 Replies
View Related
Feb 27, 2009
I have a spreadsheet with about 20,000 lines. (The number of rows fluctuate) I need a macro to remove (delete) rows that contains the phrase "Off Peak" and "Weekend"
I was setting it up in a loop but I don't know how to make the loop end after ALL the "Off Peak" and "Weekend" info is removed.
My macro just does a find then delete the row and then loop to the find again. Is there an easier way and can the "Off Peak" and "Weekend" statements be combined into one search
Do While
Cells.Find(What:=" Off Peak", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
Selection.EntireRow.Delete
Loop
View 9 Replies
View Related
