Sumproduct Multiple Values In One Column
I am trying to sum up the rows that have multiple values in one column.
Here is my curent formula THAT works
=SUMPRODUCT(($H$46:$H$5787="EODeal ProcessingClosing")*($K$46:$K$5787="Submitted")*($I$46:$I$5787="2medium"))
Now I also want to add the following
($K$46:$K$5787="Assigned")
How can I get the value I need so that column "K" I get returned both "submitted" and "assigned"?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Multiple Ranking: Rank The Values In Column B And Then Rank The Values In Column C
What I am trying to do is give the rank in column D based on the values in columns B and C. Some of the values in column B will have then same rank, and as such I want to add further criteria on which to rank them. I would first like to rank the values in column B and then rank the values in column C, which should give the rank in column D. For example Dog and Frog have the same value of 400 from the Non UK column. Therefore, rather than having these as both rank 1, I want them to be ranks 1 and 2, so want to add another criteria (UK). As Dog is greater than Frog in the UK (i.e. 10>7), I would like to rank Dog as 1 and Frog as 2. Goat will be ranked as 3 because it had the thrid highest value in the Non UK. ABCD 1Non UKUKRank 2Cat20055 3Dog400101 4Eel200114 5Frog40072 6Goat30023
View Replies!
View Related
Ho To Get Multiple Column Values In One Shot Thru Vlookup
My problem is how do i get multiple column values at one shot. For example in one excel sheet i have columns A,B,C,D,E and in A column i have all the Partner ID's and rest of the columns i have the data. Now in other excel file I have Partner ID's which are not in order...now i want the data in all 5 columns according to partner id's from the previous sheet i need to do a vlookup function for five times to get the same data....is there any way that we can do it in one shot.
View Replies!
View Related
Values In Column Based On Multiple Criteria
Option Explicit Dim lastrow As Long, t As Long Sub Method() lastrow = ActiveSheet.UsedRange.Rows.Count For t = lastrow To 1 Step 1 If Cells(t, 8).Value <> "" Then If Cells(t, 9).Value = "Y" And Cells(t, 10).Value = "" And Cells(t, 12).Value > _ 6 And Cells(t, 12).Value < 60 Then Range(t, 25).Value = 20 End If Next t End Sub Alright, the above code is not working. I am not sure if it is the write part (t,25 value) that is wrong. I want the Y column to be written with a method numbered "20" if the conditions (H is not null, J="Y", K="", and 6<M<60). I have numerous other methods to put in. The reason I'm not doing Case Statements is this is jsut to write the basic code, and then I will have to move it over to ReportSmith using ReportBasic.
View Replies!
View Related
Comparing Values In One Column And Inserting Multiple Blank Rows
I am working on formatting a spreadsheet report where the values will change in column A. Here is what I would like to do via a Macro. Compare the cells in column A (e.g., compare A2 to A3, compare A3 to A4, and so on). If the values between the two cells in column A are different, insert three blank rows and set the active cell to the next cell following the blank lines. Example: if cell A5 is different from A6, insert three blank rows below row 5 and new active cell is now A9 and the comparison would start again. I have been trying to code the macro for this but with no success. Here is the macro I have been working on. Sub Macro1() Const NumRow As Integer = 3 Dim StartCell As Range Dim RowNR, NewCnt As Long Dim RowCount As Long Dim Count As Long Dim intRow As Integer Dim bFmtComplete As Boolean RowCount = Application.WorksheetFunction.CountA _ (Range("A1", Range("A" & Rows.Count).End(xlUp))) bFmtComplete = False RowNR = 2 Range("A1:J1").Select ' Rows("1:1").Select Selection.Copy................
View Replies!
View Related
Sumproduct  Values Across Three Columns
i have information across three columns the first has usernames in each row the whole way down, the second has between 17 activity codes (when not eacher user will use), the third has the times they have been on these codes. what im trying to do is match the name, code and get the time to be displayed in a fix table, as the reported information is not always in the same structer eg user1 code 1 0:02:00 user1 user1 user2 code 3 0:05:00 user2 code 6 0:20:00 user2 now i've got it in my head that sumproduct iwll be the best way to get it, but i cant seam to get the third array to work properly, and always comes up with either value or NA
View Replies!
View Related
Count The Values Through Sumproduct In VBA
The problem facing by me that I have a worksheet in which I count some values through sumproduct function in vba but its not working but if i manually put in this in sheet it works.here is the code. Dim Sal As Workbook Dim rng As Range Dim rng1 As Range Dim Dept As Range Dim Dept1 As Range Dim rg As Range Dim i As Byte Sub salries() Application.DisplayAlerts = False On Error Resume Next Set Con = Workbooks("Branch Wise Deparment Wise No. of Staff.xls") Set Sal = Workbooks("salarysheet.xls") Sal.Activate Sheets("Working").Delete Sheets("GT").Activate Range("B3").Select Set rng = Range(ActiveCell, Selection.End(xlDown))..............
View Replies!
View Related
SUMPRODUCT Multiple Variables
I trying to figure out how to calculate a field based off multiple variables that are dependent on another cell range. I'm looking to count everything in the C8:C49 cell range that contains either "BETA" or "FINAL" in the cell but ONLY if the F8:F49 cell range contains "In Test")
View Replies!
View Related
How To Use SUMPRODUCT With Multiple Criteria
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.
View Replies!
View Related
Multiple Lookup Or Sumproduct
My data range is E2:H15. A separte calculation would generate a number in C2. C4 uses Index(match) to finds the closest Vol match greater than or equalin column E. Column E is sorted Desending for the Match. The user then inputs two maximum values for W (column F) and H (col G). The example problem should return 20 for L. Help! I have been trying every combination of index, match, lookup etc. Sheet1 *ABCDEFGH1****VolWHL2*Minimum Vol463*800104203Lookup 1Closet Match Greater Than or Equal To480*64084204Lookup 2Input Max W =8*600103205Lookup 3Input Max H =3*600104156****48083207*Solve for L20*48084158****48064209****4501031510****4001041011****360831512****360632013****360641514****320841015****30010310Spreadsheet FormulasCellFormulaC3=INDEX(E2:E15,MATCH(C2,E2:E15,1),1) Excel tables to the web >> Excel Jeanie HTML 4
View Replies!
View Related
Sumproduct With Multiple Conditions
My sumproduct has multiple conditions  is there a limit to the number of multiple conditions one sumproduct formula can have? I didn't think there was???? The formula looks like this, and should return results  at the moment, it returns #N/A. Does it have anything to do with the fact that I'm using named ranges? =SUMPRODUCT((Data!W:W>=Cumulative!A12)*(Data!D:D=Super)*(Data!E:E=Region)*(Data!Q:Q=EWC)*(Data!J:J="H")*(Data!L:L="Tonnes"),Data!K2:K65536)
View Replies!
View Related
Multiple Vlookup Or Sumproduct
Column B has the "date" Column D has the "time" Column E has the "field" then in Column F is "VIN" and Column G is "HOMI" Then I have another area on my page that I need to redistribute data. I need a vlookup, sumproduct, or something that will give me the data I want. here is what i am looking for: R138 through AE300 is the data area. Column R has the "date" Column T has the "time" Column V and W are "field 1" Column X and Y are " field 2" Column Z and AA are "field 3" Column AB and AC are "field 4" Column AD and AE are "field 5" I want to be able to put a formula in the field areas V138:AE300 that will find the "VIN" and put it in my new area and my "HOMI" and put it in the new area. Is this impossible? Or is this just wishful thinking? If I need to give anymore info let me know. I cannot add any software at work so I can't show you the data I am talking about, unless i can copy and paste the sheet to show you.
View Replies!
View Related
SUMPRODUCT For Multiple Worksheets
I have never used Mr Excel so here goes. I have two text columns and a column with numbres in a worksheet called 'SZU new'. In another 'summary' worksheet I am able to return a total number where the two conditions are met by entering in a cell: =SUMPRODUCT((AA$6='SZU new'!$D$9:$D$2478)*('GROUP (parc)'!$B10='SZU new'!$B$9:$B$2478)*('SZU new'!$H$9:$H$2478)) BUT how do I adapt the formula above so that I can get a total number meeting the two conditions in ten different worksheets and with a varying range of rows eg the data could be from $D$9:$D$500. I don't want to repeat the above formula ten times, even if this was possible in excel.
View Replies!
View Related
Multiple Criteria  SUMPRODUCT
I'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.
View Replies!
View Related
Sumproduct Across Multiple Worksheets
The following formula works, trying to shorten the formula using sumproduct and indirect. Not sure I'm writing it correctly =IF(SUM('1:31'!X298)=0,"0%",SUM(((S40*'1'!$X$298)+(S41*'2'!$X$298)+(S42*'3'!$X$298)+(S43*'4'!$X$298)+(S44*'5'!$X$298)+(S45*'6'!$X$298)+(S46*'7'!$X$298)+(S47*'8'!$X$298)+(S48*'9'!$X$298)+(S49*'10'!$X$298)+(S50*'11'!$X$298)+(S51*'12'!$X$298)+(S52*'13'!$X$298)+(S53*'14'!$X$298)+(S54*'15'!$X$298)+(S55*'16'!$X$298)+(S56*'17'!$X$298)+(S57*'18'!$X$298)+(S58*'19'!$X$298)+(S59*'20'!$X$298)+(S60*'21'!$X$298)+(S61*'22'!$X$298)+(S62*'23'!$X$298)+(S63*'24'!$X$298)+(S64*'25'!$X$298)+(S65*'26'!$X$298)+(S66*'27'!$X$298)+(S67*'28'!$X$298)+(S68*'29'!$X$298)+(S69*'30'!$X$298)+(S70*'31'!$X$298))/(SUM('1:31'!X298)))) This is the formula I'm trying to write but get #value error. {=IF(SUM('1:31'!X298)=0,"0%",SUMPRODUCT(S40:S70,INDIRECT("'"&ROW(INDIRECT("1:31"))&"'!X298"))/(SUM('1:31'!X298)))}
View Replies!
View Related
Sumproduct  Select All Values Per Wildcard
Sumproduct formula with selection criteria of "A", "B"... in the first column and numeric values in the next colum. The selection is controled by a List where the user can choose "A", "B", ... ,or "ALL". What wildcardtype (pseudo) is needed to select all values when "ALL" is chosen? I'm using Sumproduct because there is other selection criteria, but it should not impact this part of the formula. Example: Sumproduct((A1:A100=X1)*(B1:B100)) , where A=selection aray, B=numeric value, X1=corresponding list selection to A
View Replies!
View Related
Sumproduct Formula  How Do I Exclude Values
I've used the sumproduct formula very sucessfully in a workbook. The workbook is used to monitor discrepancies routed to other departments. Column U has the status of the discrepancy (Open, Closed, Cancelled etc). The below formula returns the number of discrepancies raised to a particular department. Now I need to tweak the formula to exclude values "Cancelled" found in range $U$119:$U:417. =SUMPRODUCT((Register!$I$119:$I$417=$A4),(Register!$C$119:$C$417=B$2),(Register!$B$119:$B$417))
View Replies!
View Related
SUMPRODUCT Macro With Multiple IF Statements
I'm needing a macro that will allow me to get around the limits of no more than 7 IF statements and using a SUMPRODUCT formula as well. I need the total or sum of the macro/formula to be in cell "DB8". Here's my formula: =SUMPRODUCT(IF(CP8=M11,EXACT(K7,"DI"),0)+0+SUMPRODUCT(IF(CP8=W11,EXACT(U7,"DI"),0)+0+SUMPRODUCT(IF(CP8=AG11,EXACT(AE7,"DI"),0)+0+SUMPRODUCT(IF(CP8=AQ11,EXACT(AO7,"DI"),0)+0+SUMPRODUCT(IF(CP8=BA11,EXACT(AY7,"DI"),0)+0+SUMPRODUCT(IF(CP8=BK11,EXACT(BI7,"DI"),0)+0+SUMPRODUCT(IF(CP8=BU11,EXACT(BS7,"DI"),0)+0+SUMPRODUCT(IF(CP8=CE11,EXACT(CC7,"DI"),0)+0+SUMPRODUCT(IF(CP8=CO11,EXACT(CM7,"DI")+0,0))))))))))
View Replies!
View Related
Multiple Criteria Countif Or Sumproduct
I haven't been this deep into excel before. The deeper I look, the more potential I recognize, the more amazed I get. That being said, I have come to a tough count issue. Let me attempt to explain as precisely as possible. My current worksheet is large but I am only particularly concerned with two columns of information (Regions) and (Days). The logic I am attempting is something along the lines of Count If Region = East, or West, and Days is greater than 0, less than 60. I am open to any and all suggestions on how to tackle this situation. I have been able to achieve similar counts by using pivot tables but the dynamic nature of these two columns presents some difficulties that my “new user” mind has been unable to work through.
View Replies!
View Related
SUMPRODUCT  Count Multiple Conditions
Ive started using the sumproduct function to count multiple conditions which is useful howveer if i want to count those records in one column that meet a condition and those records in another column that meet anyone of a number of conditions how can i do that? the only way i can think is like the below =sumproduct(((columnA=apple)*((ColumnB<>Red)*(columnB<>Yellow)))) Rather than having to eliminate red and yellow i would like to say is green or blue.
View Replies!
View Related
Multiple Criteria Met In A Sumproduct Formula.
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 selftaught 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.
View Replies!
View Related
Lookup With Multiple Criteria...sumproduct
Attached is my sample workbook. There would normally be 600+ employees with multiple rows per employee. I would like Cell O3 in the Premium Calculation Worksheet to look at the Premium Contribution Report, and if Row A contains the employee number (A3) AND row C contains "H&D" I would like it to sum row E. I included the sumproduct formula I tried to put together but I'm getting an error, so I'm not sure what I've done wrong. The reason I have it referencing "O2" instead of just inputting "H&D" is that O2 could be any number of plans  I have multiple rows with different plans and I need it to pull in all the data.
View Replies!
View Related
Using Multiple Sum Ranges In Sumproduct() & Countif() In Array
My problem is : 1.In G Column I put logic for Fail and Obtained Marks. G2=IF(COUNTIF(B2:F2,">=60")=5,SUM(B2:F2),"Fail") 2. Now in H column I want use this formula which I obtained from this forum H2=SUMPRODUCT((G$2:G$7>G2)/COUNTIF(G$2:G$7,G$2:G$7&""))+1 To get the position of Students. But the text value "fail" in the G2:G7 getting Position No. 1 and i've noticed the reason by using evaluate formula as well. 3. I got solution by changing "Fail" with 0 by creating column I and then column H put this formula ........
View Replies!
View Related
Multiple Date Based SUMPRODUCT Failing
I have the the need to show the sum of the product of sheet 2 on sheet 1 if several conditions are met. The formula is working except for the first array: =SUMPRODUCT((Bid_Circuits=$A2),(Bid_Week_End=MONTH(D2)),(Bid_Week_End=YEAR(D2)),(Bid_Completed)) When I use XL's evaluate feature, XL seems to find the proper data yet returns #VALUE!
View Replies!
View Related
Sumproduct Where Two Conditions In The Same Column
I am trying to get sumproduct to work on a table where two conditions in the same column must be true (ie two employees must both have worked on the same project) in order to calculate a result. Trouble is my formula doesn't produce anything but a big fat zero. Column A contains the list of projects. Column B has the list of employees. And column C has each employee's cost. So: =sumproduct((column A = project1) * (column B = Joe) * (column B = Mark) * (column C)) =total cost of Project1 when both Joe AND Mark work on it. Unfortunately, when I structure sumproduct this way, it returns zero.
View Replies!
View Related
Sumproduct (add A 3rd Column Of Criteria)
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.
View Replies!
View Related
Finding Multiple Values To Return Multiple Values
I have a bill of materials with a description column. I want to search that column for various words (ie. wheel, screw, spacer, shelf, etc) and return a value into another new column depending on that value (wheel inputs wheel, screw inputs hardware, spacer inputs hardware, shelf inputs shelf). How Excel shows you how to search will only return one value because I can't use an else statement:
View Replies!
View Related
SUMPRODUCT With Multiple Criteria: Count The Number Of Documents
I have attached a spreadsheet with a small indicative data set to assist in understanding. I am trying to count the number of documents each individual has assigned to them that are not yet 'completed' (ie REGISTERED, IN WORK, REVIEWED). The problem I am trying to overcome is that the document state can be 1 of several values indicated in the same column. I have tried using this SUMPRODUCT formula: =SUMPRODUCT((($E$2:$E$11="REGISTERED")+($E$2:$E$11="IN WORK")+($E$2:$E$11="REVIEWED")*($B$2:$B$11="Jones"))) but it is generating incorrect values! Specifically:  Jones shoulld return 1  Franks should return 3  Smith shoudl return 0
View Replies!
View Related
2003: COUNTIF/SUMPRODUCT, Multiple Criteria W/Wildcard
I'm trying to write this but it returns a 0 when I know there are 3 records that match this criteria: =SUMPRODUCT(('InvoiceDetail'!J2:J50="NewJob_Post.NET")*('InvoiceDetail'!H2:H50="KY_*")). I think the problem is in the wildcard character. I don't know if I should be using COUNTIF or SUMPRODUCT or something else?
View Replies!
View Related
Whole Column Range In A Sumproduct Array
I've created a spreadsheet with SUMPRODUCT formulae, which is working fine for now. However, these formulae include arrays with ranges of, for example, $G$2:$G$600. What we need to do is, instead, reference the while column as far down as it goes, forever as the range of the array. This applies to multiple occurrences. Every formula I have found for this may work on of itself, but does not work with the SUMPRODUCT formulae I have used. For reference, an example: =SUMPRODUCT(('BG NEW DB'!$D$2:$D$600=$B$2),('BG NEW DB'!$G$2:$G$600=C$2),('BG NEW DB'!$H$2:$H$600=$A$2),('BG NEW DB'!$AD$2:$AD$600="Y"))
View Replies!
View Related
SUMPRODUCT Not Working Due To Text In Column
I'm trying to work out how to fix the formula below to take into account and ignore and text entries, while giving me the result of the sum of column K minus the sum of column J. If I delete the text entries, the code works but I need the text entries to stay where they are. I've attached a sample sheet with fake info to explain whan I'm trying to do. Cell N28 on the 'MGMT INFO' tab contains the following formula: =IF(ISERROR(SUMPRODUCT((Sheet01!$K$1:$K$1000)(Sheet01!$J$1:$J$1000))),0,(SUMPRODUCT((Sheet01!$K$1:$K$1000)(Sheet01!$J$1:$J$1000)))) Columns J and K on the 'Sheet01' tab contain the Pay and Invoice information for all the work planners for that client that I'm trying to find the difference between. Each work planner has 'Pay' and 'Invoice' also in that column though, one entry per planner which is causing the SUMPRODUCT formula to screw up.
View Replies!
View Related
Modify Sumproduct To Include Helper Column
I'm using Excel 2007 and have an Employee Scheduling Program that keeps track of 10 employees on a monthly basis (1 worksheet per month). The days of each month are in columns (I thru AM) and my 10 employees are in Rows 6 thru 15, which creates a grid of cells. I use Conditional Formatting to highlight the Weekends, Todays Date, and Holidays. My Sumproduct formula (shown below) is in each of the cells of my grid and places a number (1 to 10 for each employee) from start date to the end date. My Current formula works great as it finds every occurrence of the argument but I need to modify it to include the contents of the Helper Column. If(Sumproduct(($g$44:$g$74=$c$6)*($m$44:$m$74<=i$4)*($t$44:$t$74>=i$4)),1,0).......
View Replies!
View Related
