Add Numbers In Column That Meet Criteria And Find Maximum Sum?
Dec 31, 2013
I'm developing a spreadsheet for a nonprofit that provides feedback on their major fundraiser/auction. I'm trying to create a formula in a cell that will look at the bidder #'s in column B and add the value of the their purchased items found in column C and return the number of the bidder that has the largest total value of purchases.
This can be done with a pivot table and give a table of all bidders, their total amount paid for items, and the number of items purchased.
View 1 Replies
ADVERTISEMENT
Apr 21, 2014
I am looking for a formula that either accepts the number in a cell if the numbers on two other cells are "approved". In other words, I have on cell A1 my value to be tested. On cells A2 and A3 I have two numbers. On cell A4 I'm looking for a formula that copies the number on A1 if A2 is bigger then 0.25 and A3 is smaller then 0.35. In not, there's no copy or a N/A appears.
View 3 Replies
View Related
Mar 13, 2014
How can u use sumifs to meet 2 critera and then sum the one column..
=SUMIFS('[South Pac Load Tracker - MAR14.xlsm]SPECIALS TEAM NZ'!$M$8:$M$100000,'[South Pac Load Tracker - MAR14.xlsm]SPECIALS TEAM NZ'!$C$8:$C$100000,B5,'[South Pac Load Tracker - MAR14.xlsm]SPECIALS TEAM NZ'!$B8:$B100000,$A$2)
That is the formula i used and it worked, yet when i try to use it for other names (where b5 would be) the numbers skew and do whatever they want..
I just want to sum all of the values in M:M which have B5 or "fiona" in columns C:C and also match the date from B:B with A2.
I believe ive done it right and as i say it does work with the first name in b5 but when i pull the formula down it doesnt work anymore...
View 5 Replies
View Related
May 28, 2009
I need to sum a column when the quantities meet two criteria. The name in one column, and the date in another column. Probably an easy formula, but new at this and having trouble.
View 4 Replies
View Related
May 14, 2008
I have a filtered list of data expanded to "all" in each column. One column is "percentile" (25%, 50%, 75%, etc).
Search down the "percentile" column until you encounter 25% as an entry, then sum a number in the same row from a different column with each 25% entry you find.
View 4 Replies
View Related
Aug 19, 2014
a macro finding the max number in col B in each day (hr 0- hr 23) and in col C put a "1" next to it.
View 10 Replies
View Related
Mar 19, 2014
I am trying to conditionally format cells in column I (named range of ETID).
I want the cell to change shading if the cells do NOT meet this criteria:
Whole Number10 digitsstart with '0' or '1'I have this but it isn't working:
=AND(OR(LEFT($i2,1)="0",(LEFT($i2,1),1)="1"),OR(LEN($I2)<>10,($I2)<>""))
View 4 Replies
View Related
Dec 8, 2008
I am trying to use COUNTIF formula to count how many item in a column that meet certain criteria, say between 10 and 20...
=COUNTIF(G1:G100,"AND(>10,<20)")
View 3 Replies
View Related
Oct 17, 2011
How do I find the minimum and maximum numbers in a table if I do not know the size of the table but can assume the user will choose the first cell (upper left cell) of the table and run the macro?
View 5 Replies
View Related
May 23, 2008
need a formula to calculate the total number of x's in one column (column B, C, E are training types= x) where the corresponding column date falls within a date range. It’s to total each type of training done for each month. I have 3 training type columns and a cell that calculates the total number of trainings for the each month:=COUNTIF(F2:F100,">="&DATE(2008,2,1))-COUNTIF(F3:F200,">="&DATE(2008,2,31)).
So now I just need it broken down by training type per month. How many x's in each column for February as an example.
View 7 Replies
View Related
Aug 9, 2013
In excel 2007 i have three sheets.
In sheet1 : Cell : "D3" : I have find out & show maximum date with two criteria (i.e. code & series) from all sheets.
Also in cell : "E3 : I have find out maximum date with two criteria from particular one sheet only.
I have mentioned comments in attach file.
In sheet name 1112 & 1213 : I have a lot of data approx 40000.
Column A : Invoice no
Column B : Invoice date
Column C : Code
Column D : oano
Column E : Name
Column F : city
Column G : distric
Column H : Series
In yellow highlighted cell i required formula.
View 5 Replies
View Related
Feb 9, 2009
I spent couple of hours trying to figure this out, but without success. Please check the attached example. I need a formula that will find the max value (number of views in English anguage) in the column "I" and display the corresponding "Headline" value in the column "B". In the example it is "Headline 5".
I can not change position of the columns, i.e. "B" must remain "B" and "I" must remain "I".
I experimented with Index and vlookup but to no success. I have outlook 2003.
View 5 Replies
View Related
Oct 9, 2008
Look in Column E and find the MAX value. Once you find the MAX value, (let's say E27) display the date that's in C27. I bet this is really easy but I've been screwing around with it for over a half hour and can't get the correct result.
View 2 Replies
View Related
May 15, 2009
I have two columns of data. The first column is the 'type' and the second column is the 'value'. I need to find the maximum 'value' for each individual 'type'
The 'types' are not necessarily next to each other and the data cannot be sorted to do so.
Example:
type value
A 15
B 6
A 21
C 7
B 13
I need to be able to say the MAX for 'A' is 21, the MAX for 'B' is 13 and the MAX for 'C' is 7.
View 2 Replies
View Related
Dec 6, 2013
I have a table with 3 columns with an unkown number of rows (text, date, date) that is being imported daily.
I want to create a 4th column with dates starting from today and each subsequent row be one day earlier. I want to look at 30 previous days.
I then want to count the number of rows (looking at column 1-3) with the following criteria:
Countif column2<= "date in colum4" AND column3< "date in column4"
View 6 Replies
View Related
Sep 23, 2007
i have spent 40 hours, and still didn't find a solution. Please help is need it!!
example:
i have to find all articles with same code (222). first one has Q =100 second one Q=250.
soled Q=150
(i am talking about 5000 rows, 400 different or same articles per month- 12 months)
columnA-----columnB---columnC
222 ----------100---------0
111-----------50
333-----------70
222----------200---------200
333---------- and so on
first i have to deduct from the first one it finds (max. till 0 ...it can not be negative) ....after finding another one it deducts the rest---it means 50
Is there any kind of formula with this possibility.
if it is poorly writen please let me know for more info.
i am not an expert in excell, but i have tried variations of sumif, vlookup functions, but i always get stuck deducting the whole Quantity from all of the same (222) articles .
View 9 Replies
View Related
Mar 25, 2009
I have a spreadsheet that is set out to look similar to a MS Project plan, with dates across the top, users in column A and Assignments in column B. In the corresponding cell that matches the user, date and assignment they will enter the number of hours they anticipate working on the assignment.
I then have a summary sheet that covers all assignments, week ending dates and users. I want to be able to calculate the hours and copy it into the cell in the summary sheet that matches the Day, User and Assignment. I have tried using SUMIF, but this requires the criteria cells to all be in columns, but with this sheet the dates are in a row.
View 9 Replies
View Related
Oct 6, 2006
I have built the following code which should look through a data sheet and then work out the instances where it meets the following criterias:
= "Client A"
= "First letter of surname is "a"
The code is as follows:
Public rowcn
Sub compare()
Dim rngTemp As Range
Dim intCounter As Integer
loop_col = "Client A"
data_sheet = "Data"
target_sheet = "Summary"
rowcn = 2
Do.............................
View 2 Replies
View Related
Apr 6, 2013
I need to count the number of occurances that meet two criteria i.e.,
Status needs to be UNSOLD
Size needs to be =>10.5 and also <12.5
Dress
Status
Size
1
UNSOLD
9
2
10.5
3
12.5
4
UNSOLD
12
5
15
View 4 Replies
View Related
Apr 7, 2014
Aim - Extract every word after: and before , and put them in the appropriate headings
I have attached my desired results in column B - E. To calculate the answer, the heading of the tile is normally just before the : Meaning the Answer is after the : and before the ,
View 2 Replies
View Related
Nov 22, 2009
On the attached example i would like a formula in F2 which sums all the values where column equals "Smith" and column B equals "Golf European". I'm assuming it would involve the SumIf formula but i'm not sure if this is the best solution when the criteria is in two columns?
View 3 Replies
View Related
Jul 2, 2014
I have a spreadsheet that has employee name and average hours worked.. The employee is listed multiple times because each row represents a pay period with number of hours worked. the last column I used to average the hours worked. So what I need is whenever there is a value greater than 30 in column g, I need to copy the row where column G has a value. It can be copied on the same sheet or on another.
View 9 Replies
View Related
Jun 23, 2009
I am trying to use this function to figure out averages for tests. there can be up to 25 tests in the sheet which is where the "YES" comes in. If the test booklet was used for this class then YES will be placed in the cell in field B2:B26.
Now thes issue I'm having is that the formula is doing the math for the fields C2:C26 if there is a number in them if there is even if it does not say YES in the B2:B26 field.
Here is the formula/function I'm using:
=(COUNTA(C2:C26))/COUNTIF($B$2:$B$26,"YES")
View 6 Replies
View Related
Apr 11, 2008
I have a list of items (TR Sets) from 1 to 96 in the range B4:B99, with values corresponding to each one (Acid Number) in the range C4:C99. I would like to make a separate list that names each TR Set (using numbers 1-96) that has an Acid Number > 0.1 . Is there a formula to do this?
View 13 Replies
View Related
Apr 5, 2012
I want to make a function that gets the range like. "A1:B9" And this function automatically gets the range when given criteria meet.
For example: I have a cell A1 with value 0 and next 0 value in cell A10. I want to put that function in C1. So this function returns me the range like " A1:B9"
View 3 Replies
View Related
Jun 18, 2013
I need a formula that will tell me if EITHER two cells = a text word. I've tried a few things and can't seem to get it to work!!!!
See in the example of my spreadsheet below: If A2 = FALSE or B2 = FALSE then D2 should display "Allowed" if either are TRUE D2 should display "Not Allowed"
Is this possible!?!?! I've tried way to many different formulas and am close to giving up..
A
B
C
D
1
Not Slow
50%
FY
Change
[Code] ........
View 6 Replies
View Related
May 30, 2014
Basically, need to count rows that have 5 columns of data in it. If all 5 columns within a row have something in them greater than one character, that row needs to be counted.
View 1 Replies
View Related
Apr 6, 2007
I want this macro todo is to start at the top of columns A to J
and work down the column and colour any cells that meet the criteria.
Range(“A1”).Select
Do Until ActiveCell = ""
If ActiveCell > 0 Then
Selection.ActiveCell.Interior.ColorIndex = 5
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub
View 9 Replies
View Related
Oct 27, 2009
I need to extract key cell info from a master list when one of the cells meets a certain criteria.
Example:
Each row of the main sheet represents a Purchase Order with columns A-M each containing text, numeric, or currency values regarding that PO. Column F represents a 4-digit activity code that defines exactly what was purchased. If the activity code for a row is in the 1000 series (1000 -1099) I need to assign info from 3 other columns (say B, E, &G) to a subsequent sheet for tracking.
My limited Excel-o-vision has built this: (sheet 1 represented as FY10)
=IF(AND('FY10!F4>=1000,'FY10'!F4
View 9 Replies
View Related
Jul 3, 2009
I want to sum all cells in column A that have one of several tags in an adjacent cell in column B.
- Some of the rows in column A are not tagged
- There are currently 3 tags that are valid in column B (I, S, R), but this could increase (or reduce) in the future, say to I, S, R, E
- The possible tags are contained in a named range, but don't have to be
- It's not possible to restructure the data because the columns are in a scratch sheet, where a variety of calculations are created on the fly in the column. I've attached an example, which might make it clear
- There are several (up to 10) sheets like this in the workbook with lots of different calculations that are then summarised in to some analysis.
- The overall objective of the workbook is to be able to;
a) include/ exclude individual calcs for any one column
b) include/exclude a country - solved
View 3 Replies
View Related
