I have 1 worksheet which consist of few products for 6 months which I want to
sum up the individual product cost for certain period ie. YTD for 3 month, YTD april, and Q2 (april to June). The result is to appear in another worksheet. Try to use sumproduct, but unable to get what i want..
i m trying to use the sumproduct formula, and OR but i cannot seem to get this right! =Sumproduct(--(A1:A10="Yes"),--(OR(B1:B10="Yes",B1:B10="Mayby")),C1:C10)
I have also tried Array Formula as follows; {=SUM(IF(A1:A10="Yes",IF(OR(B1:B10="Yes",B1:B10="Mayby"),C1:C10)))}
I have also used UDF to for the sumproduct, but cannot make that work! keep giving me value message
Function
Function Customer(Service as Range, Outcome as String, Service2 as Range, Outcome2 as String)
Customer = Sumproduct(--(Service = Outcome),--(Service2 = Outcome2), Result)
-Didnt get thru this bit to start building on the Function! keep giving me #Value!
I'm starting a dashboard, where on the front page I have two combo boxes on the left, and three empty fields to the right. I'd like the three fields to the right to auto-populate table-based values depending on the chosen criteria from BOTH fields (by store and month/date). I've attached a sample of what I've got so far. I've only provided three tables for this example, and I have a table with the same column/row titles for each metric and I have three different metrics I'd like to auto populate: COGs, Sales, and GM% or in the example, metric 1, metric 2, metric 3. No pattern in the table values, just wanted to populate the fields quickly. All fields are organized by store/month-date and I've set up a link to my combo boxes on a calculations tab.
View 2 Replies View RelatedThe Table below outlines a scenario i have..
I am looking for a sum that looks at Colum A: to determine if it is an old version and new. So G2 should If it is marked with the word "New" give me the sum to column F2: otherwise give me the the sum of B2:E2.
Was looking at Sumif but not can't seem to get the formatting right.
A
B
C
D
E
F
G
[Code] .....
I've got a sheet called DATA with a series of columns. Column Q has a series of numbers throughout the rows. I need to input a formula in a cell that says, everytime column Q is = 2, calculate the sum of those rows in column N.
The second one is a bit more challenging. A few cells in column F contain the number 1 in them. I need a formula that calculates the average of the cells in column C wherever there is a 1 in column F.
I am trying to create a sliding fee scale for a medical practice. Essentially it will categorize patients by family size and income level. The table which the scale is based off of is as follows: The far left column is family size (1-10) followed by 4 monthly income levels (ie. 1000, 1200, 1400, 1600). The table is based in the federal poverty line (FPL). I need to create a lookup formula which will reference this value and generate the appropriate category based on the income and family size of the patient. For example, according to the table a 3 person family which earns less than 1000 is in category A, but between 1000 and 1200 is in category B.
View 9 Replies View RelatedI have a complex workbook that now requires me to use different assumptions based on the label of the assumption (a "year" label) and the relevant year of the month in question (probably using the "Year(A1)" function. However, I do not know how to write a formula that will change assumptions based on whether the year of the relevant cell matches the year of the relevant assumption set. I attached a super-simple spreadsheet as a sample.
View 3 Replies View RelatedI have two workbooks. I'll call them wkbk1 and wkbk2.
I am looking at three cells in the same row in wkbk1.
I need to identify which row in wkbk2 contains those values and then return a value from a cell in the same row in wkbk2.
How do I structure this look up?
I have a sheet with 3 columns - Line, Product and amount. I have to calculat the amount based on which line it is, and which product.
I. e I want to calculate all the amount in Line 1 except if the product is 3,23,31 (just an example - the range can be longer).
In workbook added i have shown the problem. I have tried the sumproduct formula, but cant get it right!
I need to create the formula in "Sum state" sheet B2 to sum items for tab AZ (b2:b6). The formula needs to work when I drag it down to other cells.
For example if I drag formula to B3 it needs to adjust the tab from AZ to CA, and add things for that tab. Each cell from A2 :A 11 represent a state and each tab has actual info for each state
In the advanced filter, I am given two criteria one is filter by unit price by less than 1000 and order no atleast 100 .
View 6 Replies View RelatedI have a table1 (see attch.) and a "form" to someone to fill. Then I want to fill the "account" and based on the account choosed I display certain values to field "Value". I'm planning making this on VBA but im not with a clear mind on how doing this..
View 5 Replies View RelatedI have a list of people that I want to sort according to their age in ascending order. If several people have the same last name (family members), I want those to be sorted together. After clicking on the SORT-button the result should look like this:
[Code] ...
Attached File : Test2.xlsm
I have a workbook that needs to fill the column B (if the column A is populated) with Column C if the column D is "YEs" once i clicked the command button. the distribution should be even. i tried rand() but its not distributively well, so i'm thinking to work it with macro.
View 10 Replies View RelatedFormula below works for one string, "review" but i also need it to work for "audit" "project" and "done" too.... so essentially someone can go into the sheet - hit a button and remove all these lines in one go.
[Code] ......
I am currently trying to reconfigure a dataset into something more managable for our regression needs. The issue I am having is trying to automate as much of the data transfer as possible. The data set is set up as follows (for example):
Yr Tag Value
0 2 #
1 2 #
2 3 #
3 3 #
0 1 #
1 1 #
2 1 #
3 1 #
etc. where tag is a special code we have developed based on the number 1, 2, 3, and 4
What I would like to do is develop a summing formula so in year 0 I can sum all the values if the tag is equal to 1 and then I would do that for the numbers 1 through 4 in my "compilation" worksheet.
I am trying to count AE2:AE601 if AE2:AE601 <= 2 and if AF2:AF601 = the value of AH2 but the formula that I have create returns a 0 when it should be returning a 1.
Where did I go wrong? Here is what I have so far.
=COUNT((AE2:AE601)*(IF(AE2:AE601,"<=2",IF(AF2:AF601,"="&AH2))))
I've got a spreadsheet with headers in column A and in rows 9 and 10. The headers in column A start in row 11 and are sizes (i.e XS, S, M, etc..). The headers in row 9 start in column B and are names (ie. First, Second, Third). The headers in Row 10 can be either Net Sales or On Hand Units. There are then values starting in B11 to L19.
The headers in column A do not repeat but the headers in row's 9 and 10 can repeat. What I need to do is create a function for a report page that will total all of the values that match to a particular header in column A, the header in row 9 and the header in row 10. So for example I would want to get the total for any instance when the header in column A is M, the header for row 9 is Second, the header for row 9 is Net Sales.
I've attached a sample of what my data dump would look like as well as the separate reporting worksheet where I want the totals to appear.
I have a sheet of around 5000 rows, I would like a macro to do some manual work for me.
Is it possible for a macro to insert a row if a "1" is present in column I. If possible I would like the row to be inserted above the 1.
VBA code to summarise a column based on 2 other columns.
All flagged codes must be summed and all non flagged codes summed.
code.....amount.....flag.......summerised code..sum amount...flag
aaa100...yaaa200...ybbb100...ybbb200...yaaa100...yccc100...ybbb100...yaaa300ccc100...ybbb300
aaa100ccc200aaa100bbb100bbb100ccc100ccc100aaa100bbb100
Sheet1 *BMNO1GenderAge GroupTotalRank2Male6-72103*3Male6-71437*4Female6-71294*
I will have 200+ records similar to the above. I would like a formula that ranks the totals. The Males in the 6-7 Age Group would be in 1 Ranking while the Females in the 6-7 Age Group would be in another.
I prefer a formula over a macro. My original thought was =rank(n2,offset(?,?,?,?,?),1) but alas I'm missing something.
I have 3 columns of data
Months Date Description Royalty
1 11/1/2010 XTY 5555
2 12/1/2010 xxx 4444
about 100 or more lines of data
Months is the running count of the number of months
Bottom line: Starting at 3/1/2011, I need to know how many months did it take to earn $125,000 (Royalty)
I am trying to sum values based on 2 criteria -- the date (using a fiscal month) and a Category (A,B,C,D). Here's a small portion of the source data:
Sheet3
JKL1DateValue Plant21/28/2012 50.00 A31/31/2012 10.00 B42/6/2012 40.00 C52/10/2012 35.00 D62/15/2012 8.00 A72/17/2012 100.00 B82/21/2012 50.00 C92/23/2012 10.00 D102/27/2012 40.00 A113/2/2012 35.00 B123/6/2012 8.00 C133/8/2012 100.00 D143/12/2012 50.00 A153/16/2012 10.00 B163/20/2012 40.00 C173/23/2012 35.00 D183/26/2012 8.00 A193/28/2012 100.00 B
And here's the result I want:
Sheet3
NOPQRS1StartEndABCD21/28/20122/3/20125010 32/4/20122/10/2012
403542/11/20122/17/20128100 52/18/20122/24/2012 50106
Feb Total58110904572/25/20123/2/20124035 83/3/20123/9/2012
810093/10/20123/16/20125010 103/17/20123/23/2012
[Code] ...........
I am trying to find the best way to give a result based out of two criterias. My original list is 1000+ row long, so I am expecting that my vba code will be refering to a worksheet with this list on it.
The list will look like this :
Group SubgroupName1aaaname11bbbname21cccname32cccname42dddname42dddname5
I will always have the group and subgroup available and I want to get the name.
So I would like to write a function or a macro that would look for the specific group and subgroup and return me the name.
I would like to obtain the below issue using VBA. I would like to delete the row if the criteria hasn't met. For example,
If cell "B3 = Dec-12" and if cell C3 is not equal to "31" then delete the entire row.
If cell "B4 = Feb-12" and if cell C4 is not equal to 29 then delete the entire row.
If cell "B5 = Feb-13" and if cell C5 is not equal to 28 then delete the entire row.
This need to be applied for the last row in column A.
Basically I am looking if column B has month and Year (i.e. MMM-YY) and if column C doesn't have that particular month days then the entire row has to be deleted.
There are two workseets involved. Inventory is the main and Pending is the second. There are 28 columns of data in each that match based on row 1 headers. I need to figure out how to cut all the rows with "Pend" listed in Inventory column Z to one row below the data in column A of the Pending worksheet. The data can be dates and also notes but no matter what is in column Z it must be cut and moved.
View 6 Replies View RelatedI have a table that has multiple products and price points in it and I would like to add a field that ranks the products by price but for only against that product.
Product Price
Result needed is the Rank only by product based on Price
A
150
2
A
155
3
B
130
1
[Code] ........
trying to lookup data based on 2 criteria. On sheet A I need to lookup "Date of Reason" and "AMT" based on Employee Name and if there is something listed in the Reason Codes Column from Sheet A. So on sheet A employee Vandiver has a reason code = Absent so I need to pick up the date and amount from sheet B = 4/21/2014 and 8. I tried the formulas listed below but can't seem to get them to work. Also need to note that sometimes the Reason Code column in Sheet A may have multiple listings so it would be better if formula used a "not blank" in it. That's what I tried with the first "IF" formulas using "=" as there are formulas in the reason codes column in Sheet A.Sheet A
Employee
Mon
Tue
[Code].....
In range B1:C5, I want to return the value in column B to column A if column C is empty as shown in the following table.
Coml A Coml B Coml C
S1S1
S3S2 89
S4S3
S4
S5 67
I'm trying to think of how to create a code that would autonumber based on criteria. I am trying to autonumber a type of invoice for an estimate.
1) Use the initials of the person creating the at the beginning of the number - we have 4 users of the form.
2) The form is an original and once completed it would be saved as an alternate file - maintaining the blank original.
3) Would it be possible to keep a running list (on a hidden sheet) so that the autonumber knows what number to use next.
I was thinkiing that it could be generated at the press of a button, since these estimates would be looked at again and again. I wouldn't want it renumbering everytime the workbook is opened.