The current spreadsheets add up each persons totals by matching the name in each tab with the name of the person who won the job located in service orders tab. BUT.....If two salesman pair up on up on a job then the formula doesn't recognize the joint name. eg Scott/Ash in row 21 & 22 (Service orders). I need the totals to half the job and add it to the salesmans total accordingly.
View 4 RepliesI have a sumproduct formula based on some criteria, but I don't know how add another criteria wherein I need to exclude in the count if the date in column F is 1/1/2009
Attached excel file for reference. LE26dec.xlsx
I have 2 columns of data being populated by vlookups
Column H is both numbers and text. Column I is Text and blanks. I need to be able to find only numeric values in column H greater than 0 and compare those occurrences with the corresponding cells in column I and if column I has a text entry (not a blank space) than to count that and at the end give me a total number of times these 2 criteria are met. As an example.
If column H has a text entry then don't count it.
If column H has a number less than zero then don't count it.
If column H has a number greater than zero but column I is blank then don't count it.
If column H has a number greater than 0 and column I has a text entry then count it.
I've tried using many variations of a sumproduct formula and none of them work.
This formula counts all instances where column I has a text entry without checking column H for a number greater than 0.
=SUMPRODUCT(--(H2:H110>0),--(I2:I110<>" "))
Or it's possible that the formula is counting the text entries in column H as a number greater than 0 but I'v tried excluding text using this..
=SUMPRODUCT(--(H2:H110>0&<>"*"),--(I2:I110<>" "))
but this causes an error in the formula somehow that I can't figure out. I even tried this
=SUMPRODUCT(--(H2:H110>0&"*"),--(I2:I110<>" "))
and I get a formula that counts only the times text appears in column H and column I together which is not what I want either.
I'm self-taught on Excel so I know there's a lot I'm not understanding about creating formulas like this but I need to have this working by Friday and I just want it to work.
In a macro I want to automatically insert a formula in several cells. The formula depends on other cells, and I want to be able to manipulate these cells with variables. Here is my code:
[Code] .....
All my variables are declared:
Sheets("DATOS YTD 2014").Cells(I, Semana + 1) is the cell I want to be able to chose due to the variables
Sheets("DATOS YTD 2014").Cells(I, 3) is the reference cell for the VLOOKUP formula, that will vary with I
SheetMonth.Range("B3:W172") is a range in the sheet SheetMonth which is fixed
The error message I receive is the following: "Run-time error 13, Type mismatch"
I precise that the next step is to make the ",6," part in the vlookup formula also variable... but I guess once my current issue is solved this one will be a piece of cake.
I have a small problem using the ‘sumproduct function’ which I am using it to calculate the area under curves using the following equation (=SUMPRODUCT((B10:B109-B9:B108)*(D10:D109+D9:D108))*0.5). However I have hundreds of curves to calculate the area under and the length of the data series for each curve is different, I am currently changing the length of the data series by hand. I have attached an example worksheet of 2 sets of data (the formula I am using at the moment is highlighted in blue – row 203). Is there are function or formula that I can use inside this equation to change the range of the formula depending on the length of the data series?
View 2 Replies View RelatedHave a perfect working spreadsheet, but now need to extract certain bits of data depending on one criteria.
Each row contains data in different columns.
What i need to do is depending on the criteria of a cell (i.e. whether its Y or N) i would like to extract certain cells onto another worksheet all within the smae work book
i.e for example
If i have columns A,B,C,D,E and F and then x amount of rows (as these can change as data is entered) if a Y is entered in F then it extracts the data out of columns A, C and D
I have a table with several criterias (Name of supplier, date, amount paid to this supplier). This table is my inputs. Each time I have an expense, I put down the name of the supplier and the amount of the expense and the date when it occurs.
I have another table with name of each supplier in rows and the date in column. Sometimes I have 2 amount for a same supplier for the same month. I would like this amounts sum in my table. I cant find a good formula to do it efficiently.
Obviously I can do a sum
(if(A2=B2, if(c6=c8);b10)+sum(if(A2=B2, if(c6=c8);b10)+ etc...
But I can 150 expenses per month...
I Have want two find the dates and the data of these dates
depending on the two criteria i have
note"the two criteria would be left(b3,4) and right (b3,4)
ex
in f3"date" i want to find the dates correspond with these two criteria
and then find the data of these dates in G3 Depending on the two criteria too.
Sheet1
ABCDEFG2DateData
Criteria1Criteria2DateResult31/1/2008M111 GG1G M113
M191M1131/2/2008M191 G1GG M11341/2/2008M191 G1GG M113
1/5/2008M191 G78GG M11351/2/2008M111 GG1G M113
1/8/2008M191 G1G9G9 M11361/3/2008M141 GG5G M113
71/4/2008M118 GG6G M113
81/5/2008M191 G78GG M113
91/5/2008M141 GG5G M113
101/6/2008M111 GG2G M113
I have attached a spreadsheet.
I have a dump of about 4000 calls (I have minimised the attachment for size sake) that I need to do certain reports on.
The second sheet (dump) has the data in its raw form from our call reporting tool. The 1st sheet (Report) is a broken down version the powers that be wish to see.
My formula needs to meet certain criteria, as you can see I only have 6 columns on the (Report) sheet where as the dump has lots !
The (report) sheet needs to list the Parent Case ID only if the Summary column has BSR at the beginning, if it meets this criteria then it lists the other relevant columns in the (report) sheet.
I'm trying to find a way that I could get the MAX value in a group of cells depending on a critieria. The situation is that I have a list of numbers and a list of dates parallel to it. I would like to find the MAX for, say, the numbers corresponding to the month of June. I would like to be able to change the month and get the MAX for each month on request.
Eg.
18-MAY-2007 600
01-JUN-2007 500
02-JUN-2007 400
28-JUN-2007 600
12-JUL-2007 700
17-JUL-2007 200
I need formula that will give me "600" if the month I want is June. Ideally I would not want to have to filter the data, so SUBTOTAL would probably not work for me.
I am trying to hide or unhide columns E:BL depending if the the cell value in row 54 contains a value (Note cells in row 54 contain formulas). I have written the following code but get a runtime error on the else statement.
Sub UpdateCashflow()
Application. ScreenUpdating = False
ActiveSheet. Unprotect Password:="LD"
ActiveSheet.Select
Dim c As Integer
For c = 5 To 64
If Cells(54, c) = "" Then
ActiveSheet.Range(Cells(54, c)).EntireColumn.Hidden = True
Else
ActiveSheet.Range(Cells(54, c)).EntireColumn.Hidden = False
End If
Next c
ActiveSheet.Protect Password:="LD", DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.EnableEvents = True
'
End Sub
I should be able to do this by now but I keep getting an #NA error. I am
trying to use this sumproduct formula
=SUMPRODUCT(($I$7:$I$2585=921),--($L$7:$L$2585="Fb")--(AS7:AS2585)) where I
am looking up store #"921" and Attribute "fb and sum where these two criteria
meet. The column I want to sum is AS7:As285. What am I doing wrong.
In Column A1:A20, these are included 5 types of data (red, orange, yellow, green, blue); in column B1:B20, these are included the total salary amount, and in column C1:C:20, these are included "Sole" , "Joint" or "Partnership", so if column A is included red/yellow/green (excluding orange and blue) and column C is included Sole/joint/Partenship, and total salary is between 6000 and 9000, then calculate the total salary of all the customers. How can I use sumproduct function to fix it?
View 8 Replies View RelatedI have a download from an accounting general ledger which has the following:
Column A: Category Description
Column B: Country
Column C: Department
Row 1: Months
Note: Each row contains the last 12 months worth of costs
As the categories/criteria can appear multiple times, and there are thousands of lines, I have been using SUMIFS to calculate totals my required combinations (eg. Travel expense, Germany, Sales department)
Every month, the information refreshes to show the most recent 12 months worth of data. And this means I need to manually update my formulas to correct the month column headings, as everything moves by 1 month.
Would something like a SUMIF with a SUMPRODUCT work? Eg. if current month = July, then it would sum everything from the July column automatically?
I want to colour 10 cells (A1-J1) if I type in yes in cell K1. Using conditional formatting Im only able to colour 1 cell. Is this possible without using VBA?
View 5 Replies View RelatedI use Windows Vista and Excel 2007.
So to cut to the chase :
On my spreadsheet, I have 6 main groups of columns, each one comprising 4 columns with their own automated calculations across the range.
Group 1: Cell H5, Cell I5, Cell J5, Cell K5
Group 2: Cell L5, Cell M5, Cell N5, Cell O5
Group 3: Cell P5, Cell Q5, Cell R5, Cell S5
Group 4: Cell T5, Cell U5, Cell V5, Cell W5
Group 5: Cell X5, Cell Y5, Cell Z5, Cell AA5
Group 6: Cell AB5, Cell AC5, Cell Ad5, Cell AE5
Then I have two columns following those: Col AG, Col AI
In each one of those two columns, I am trying to sum up a total value using The IF Function.
In Cell AG5 : the sum of the values of cells K5+O5+S5+W5+AA5+AE5,
ONLY IF the values of Cells H5, L5, P5, T5,X5, AB5 is equal to AG3 (which is a symbol (wingding 3 for capital P), this occurs 3 times out of 6, sometimes twice only, and at random, so I need the sum of 3 cells (or 2) out of 6, based on the above criteria.
Same in Cell AI5: sum of the values of cells K5+O5+S5+W5+AA5+AE5
ONLY IF the values of Cells H5, L5, P5, T5,X5, AB5 is equal to AI3 (which is a symbol (wingding 3 for capital L), this again occurs 3 times out of 6, sometimes twice and again at random, so again I need the sum of 3 cells (or 2) out of 6, based on the above criteria.
Using the formula: =IF(T5=AI3,(W5),0) , I retrieved the value of cell W5 into cell AI5; and when I tried the formula :
=IF(T5=AG3,(W5),0) , in cell AG5, it returned the value 0 which is correct again, as T5 was equal to AI3 but NOT to AG3.
But I cannot work out how to add all of the three values relating to each symbol, independently in each column, as they each occur randomly.
This is the way it looks in the spreadsheet:
Cell H5 Cell I5 Cell J5 Cell K5/ Cell L5 Cell M5 Cell N5 Cell O5/
Symbol (for P) Text Text Value /Symbol (for L) Text Text Value
And so on 4 more times across.
I could of course add it all manually for each line but I would rather have a formula to do it for me as all the other calculations are automated and/or conditionally formatted.
Criteria
Column B
Constant
Variable0
Variable1
Variable2
Variable3
2
[Variable2/Constant]
5
123
43
45
76
[Code] ..
This is my table. What formula should I use so that Column B shows the calculation depending on the Criteria column?
I am trying to match 3 values, and return a matching 4th from a chart. It will first look at one value and see if it falls between values (the LOAD value in cell B1..it will look to see if it falls between the "low limit" and "high limit" in columns A10 to A135 and C10 to C135, respectively. Then it will look for a match for the values in cell A2, and in cell A3. the return value will index to the corresponding value in column G10 to G135. I have written a formula to do this in cell D2....but it will only ever return the value in cell G10.
See the attached excel file : nozzle tester.xlsx
I am looking around any way wherein I can sumproduct the values as given in attached sheet, basically I wanted to know the total MRP value of Sale and Stock
View 3 Replies View RelatedI'm working on a spreadsheet at work and I'm trying to sum only the values in a column that meet criteria in the column next to it. For example, in the attached spreadsheet, I would like to have a totals line at the bottom of the spreadsheet for all three Facilities and the total next to them. I know I could do a pivot table but I know this approach would be more aesthetic and easier for my supervisor.
View 3 Replies View RelatedLooking to troubleshoot using a name range in a multiple criteria sumproduct formula.
For instance, this formulas works fine:
[Code]......
But, when I try to substitute the two-alpha codes as a defined name range ("keys"), it errors as "N/A":
[Code].....
An example workbook is attached : Example Workbook.xlsx
I am stuck - I have a large amount of data for a group of physicians I work for. I am trying to set up a monthly trend report to be able to run quickly after I plug in the data. I want to use some sort of lookup to look up two things - 1) the physician's specialty and 2) the month.
Can anyone look at the attached example and tell me how to do this? I have started a SUMPRODUCT formula, but am stuck on how to tell it to find only that month's data.
ID, Name, Point, Session
1111, Viking, 5, 1
2222, John, 6, 1
1111, Viking, 10, 2
What's the formula to get the Point cell value with criteria ID = "1111" and "Session = 2" ? In this case, it will return me the value of Point = 10
I have the following sumproduct formula that's providing solid results but I would like to add a 3rd column of criteria. I'v tired with little succes.
The following formula <=SUMPRODUCT(('IW 38 DUMP for Planning'!$A$1:$A$10000="2A")*('IW 38 DUMP for Planning'!$E1:$E10000={"PAA","RS","RSNR","S","SAM","SAMT","SAO","SAT","SOR","WKS"}))> totals all of the work in plant area "2A", in this case 52 records. I would like it to filter further with values in $H1:$H1000 matching criteria "CONTRACT", "MACH" OR "HTSMET".
The data is easy to find with pivot tables but I would like to take that manual step out of the reporting being doen from these records.
I am trying to get this sumproduct to work that will exclude two criteria:
1) If there is an error in the sum range (Y5:Y1000)
2) If right(H5,5)= D5& "Total"
This is the formula I have, but I'm not sure how to get the first criteria to work well, or how to get two exclusion criteria to work.
SUMPRODUCT(--($H$5:$H$1000=$D5&" Total"),--(ISERROR($Y$5:$Y$1000)),$Y$5:$Y$1000,$Q$5:$Q$1000)
I was wondering if I could use a range of cells as my criteria as opposed to inserting quotation marks with each criteria. For example in the syntax below can I do something like this? Report!$C$3:$C$5000=B45:B51?
=SUMPRODUCT((Report!$A$3:$A$5000="XXX")*(Report!$C$3:$C$5000=???)*(Report!$E$3:$E$5000))
I have the following sumproduct formula which looks in one column to search for a series of dates (greater than one date and less than another date). It then looks in column O to see if there are certain criteria if the date criteria is met. The problem is I have a lot of blank cells in column O which need to be captured.
I have this:
=SUMPRODUCT(--('Raw Data'!$B$2:$B$50000>=$H$2+0)*('Raw Data'!$B$2:$B$50000
I need to add amounts in column B, based on column A’s account numbers, so I want to add only account numbers, say 17101 & 17201 and nothing else.
View 9 Replies View RelatedI'm trying to create a budget worksheet that pulls actual data from another sheet within the file for comparison (Budget vs. Actual). There are two criteria: 1) the actual transaction falls into the same category of transaction as the budget line item (e.g., mortgage payment) and 2) the date of the actual transaction matches the month in the budget (e.g., a January or March transaction isn't pulled into the actual data for February budget information). From there, I'd like it to sum any charges or reduce by any deposits for those given criteria.
I've tried numerous things from DSUM, to SUMIF with IF, to SUMPRODUCT.
I received an answer to my original question and now have a new question but I wanted to reference my original for the history. I posted my new question at the end of my original thread.
[url]