Sum Based On Criteria

Jun 24, 2006

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.

View 7 Replies


ADVERTISEMENT

Sum Range Based On 1 Criteria Of Column & 2 Criteria Of Another

Mar 4, 2008

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!

View 5 Replies View Related

Auto-Populating Multiple Values Based On Two Criteria Based In Comboboxes

Aug 7, 2013

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 Related

SUM Based On Certain Criteria

Nov 18, 2013

The 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] .....

View 1 Replies View Related

Look Up Value Based On 2 Criteria

Sep 6, 2006

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 Related

Sum Based On 1 Criteria

Jul 18, 2007

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..

View 2 Replies View Related

Using Different Assumptions Based On Criteria

Apr 29, 2014

I 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 Related

Lookup Value Based On Three Different Criteria

Jun 6, 2014

I 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?

View 5 Replies View Related

Find And Sum Based On Criteria

Feb 26, 2009

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!

View 10 Replies View Related

Sum Tabs Based On Criteria

Feb 18, 2010

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

View 3 Replies View Related

Filter Based On 2 Criteria

Oct 29, 2013

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 Related

Listbox Based On Criteria?

Jul 3, 2014

I 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 Related

Sort Based On Different Criteria

Jul 23, 2014

I 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‎

View 13 Replies View Related

Distribute ID Based On Criteria?

Mar 10, 2014

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 Related

Delete Row Based On Criteria

Mar 14, 2014

Formula 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] ......

View 7 Replies View Related

Summing Based On Criteria

Jul 20, 2006

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.

View 17 Replies View Related

Count If Based On Two Criteria

Dec 3, 2008

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))))

View 3 Replies View Related

Get A Sum Total Based On Three Criteria

Jan 8, 2009

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.

View 12 Replies View Related

VBA To Insert Row Based On Criteria

Apr 21, 2009

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.

View 9 Replies View Related

Sum Column Based On 2 Criteria

Oct 13, 2011

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

View 2 Replies View Related

Rank Based Upon Two Criteria?

Oct 14, 2011

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.

View 9 Replies View Related

Formula Based On 3 Criteria

Feb 6, 2012

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)

View 3 Replies View Related

Sum Values Based On 2 Criteria

Feb 17, 2012

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] ...........

View 3 Replies View Related

Search For Value Based On Two Criteria - VBA

Mar 12, 2012

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.

View 9 Replies View Related

Delete Row Based On Criteria

Dec 10, 2012

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.

View 3 Replies View Related

Cut Rows Based On Criteria?

Jan 19, 2014

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 Related

Ranking Based On Criteria

Feb 27, 2014

I 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] ........

View 2 Replies View Related

Lookup Based On 2 Criteria?

Apr 29, 2014

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].....

View 8 Replies View Related

Return A Value Based On Criteria

Jul 3, 2014

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

View 2 Replies View Related

Autonumber Based On Criteria -

May 17, 2007

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.

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved