# Sumproduct Multiple Values In One Column

Jan 24, 2008
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="EO-Deal Processing-Closing")*($K$46:$K$5787="Submitted")*($I$46:$I$5787="2-medium"))

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 9 Replies
ADVERTISEMENT
Feb 5, 2014

I am attempting to count from a spreadsheet the reference number of a customer (numbers and text) based on two criteria.

1, If column G= Requested

2, Column I = Meeting

Count Row E

I thought a sumproduct was best and have started using it for the first time, I thought this should work but I keep getting a #NUM! error.

I have tried with numbers and it works but the non numeric aspect is difficult.

[Code].....

View 14 Replies
View Related
Apr 11, 2014

I'm looking at the formula in Sheet3, cell F5. You'll see that the criteria "--(Sheet2!I:I<>Sheet1!O:O)" does not seem to be working. I need the formula to not include any values from Sheet2!E:E, where the corresponding value in column I does not equal any value in Sheet1!O:O. Also, these values can change by user so its not as simple as just typing them in to the formula as an array...

That VBA part of this is that it's in a macro, so if there is any way to make this easier using code instead of a simple formula.

View 1 Replies
View Related
Sep 24, 2012

I have two sets of criteria I want to incorporate into one formula. In the first column, if the criteria is matched, it will check the criteria in the next column. The criteria in the second column is something that resembles the 'or' function. So if criteria equals x,y, or z, sum the results from the data range c3:c98. I tried writing it like this.

=sumproduct(--(a3:a98=a),--(b3:b98=x(or(b3:b98=y,b3:b98=z))),c3:c98)

View 1 Replies
View Related
Jul 31, 2014

I have 3 ranges of data which each have a quantity and a length. I want to create from these ranges a list of unique values with the total quantity required of each value (as shown). Inputs on any column may be blank but where there is a length, there will be an adjacent quantity to the left.

I'm using Excel 2010.

View 9 Replies
View Related
Feb 22, 2010

The Table : Column R represents a list of services

Column L is the full price

Column N is the discounted price ( my example reflects no discount in this case)

Column C is an associated code , in some cases the code is n/a Starting at Row 13 ........

View 9 Replies
View Related
Jun 19, 2014

Using Excel 2010, I am trying to do a Sumproduct formula with two criteria, one of which needs to ignore text values.

Here is the set up:

Column AColumn BColumn C

(Side)(Qty)(Price)

Buy5,51215.67

Sell119,428null

Buy24,20945.77

Sell20,05412.25

...

I'm trying to find the sumproduct of Qty * Price if the side equals "Buy" (or "Sell") but ignoring the "null" value in column C. The formula I have is =SUMPRODUCT(--($A$2:$A$20="Buy")*IF(ISNUMBER($C$2:$C$20),--($B$2:$B$20*$C$2:$C$20)))

The result in the cell is 0, but if I open the Insert Function dialog box, I see the correct value being returned.

View 5 Replies
View Related
Jan 23, 2009

I am trying to count the number of rows that have values greater than 10/01/2008 in either of two fields. I tried following formula but instead of giving total number of rows, it returns a random date.

View 2 Replies
View Related
Feb 10, 2010

Is there a formula to isolate observations in the same column (different values) and also all have the same column heading like the file attached?

View 2 Replies
View Related
Feb 11, 2009

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 4 Replies
View Related
Apr 3, 2014

I have IDs in the first column of an excel chart. After that I have three more columns, being date of test, type of test (start, 3 months, 6 months, 9 months, finish), and lastly the result for the test.

Right now, the same IDs are listed multiple times for different results, so for example:

ID | Date | Type | Result

27 | 3/27 | Start |8.3

27 | 6/27 |3 Mon |7.9

27 | 9/27 |6 Mon |7.4

27 | 12/3 |9 Mon |7.2

27 | 3/27 | FINISH |6.5

What I need is the following layout:

ID | Start | Date | 3 Months | Date | 6 Months | Date | 9 Months | Date | Finish | Date

ID is only shown at left, and the values for the test result and corresponding dates are shown in their respective columns.

I tried to do an IF function with a LOOKUP inside, and it worked originally, but when I add more values for the same ID to the original column, it only shows the latest date, and only gives that result.

View 1 Replies
View Related
May 1, 2014

Is there a way to collate multiple entries for the same record all into one column, as shown in the attached workbook?

View 12 Replies
View Related
Mar 14, 2014

I have a excel sheet with several columns and 2700 numbers in each column. In the first column there is standing from witch quarter to witch quarter of the day the values are (and this for several days in one sheet). I want to take the average of all the first quarters of the day, the average of all the second quarters of a day,.... But if I would do that by just sorting my table on the quarters and than manually make the formullas I would need to give in 800 formules. (way to much)

So I would like to find an easier way to take the average of column B for the valeus were A is "00:00 -> 00:15" (this are avery time 28 numbers in the month february)

In attachement you can find an example of my problem, although it isn't with real values.

View 5 Replies
View Related
Oct 23, 2009

Matching Multiple repeating values in one column with another.

I have a three columns of data that I need to map the requird Ids in Col A against multiple repeats in Col C. AS per data below ....

View 6 Replies
View Related
May 9, 2012

I have a column:

LABEL COUNT

1 of 2

empty cell

1 of 2

empty cell

1 of 2

empty cell

1 of 2

empty cell

1 of 2

empty cell

1 of 16

15 empty cells

I need to fill the empty cell with the "series" (2 of 2, 2 of 16, 3 of 16). This is just an example of data - there are all different series - 1 of 12, 1 of 5, etc.

Anyway - was hoping to highlight the whole column and be able to auto fill instead of having to extend each series. I have tried straightforward fill series which doesnt work of course. I have tried go to special - blanks but cant find right combo to make it work.

Will it work or do I have to either do manually or with a macro?

View 2 Replies
View Related
Jan 31, 2013

I am trying to pull peoples names from 3 diferent tabs into one column in a separate tab. But if a name is shown more than once only display it once. How can I do this?

View 1 Replies
View Related
Sep 20, 2013

I have a challenge which I am trying to solve without VBA and I am uncertain how to approach

If I have the following spreadsheet consisting of three columns:

FieldA, FieldB, FieldC

A........3................

B........5........6......

C.................7......

D.........................

E.........................

F........6........4......

The result I would like to achieve is

FieldA, FieldB, FieldC

A........3................

B........5........6......

C.................7......

F........6........4......

So, it means I would like to return the rows where Columns B and C have a value. Is this possible with strictly formulas?

View 8 Replies
View Related
Aug 1, 2014

I am working with an aging report where data is formatted in column. In the last column I would like to assign a value based on criteria from the adjacent columns (ex: If balance > $10,000 then value = "Hold").

My idea was that I would asign variable to the column, start a for next loop, and insert the criteria in an if then else loop. Is this the best way to go about it, should I be using case select, and how do best assign the variable to column so that I can loop down the entire report.

Here's an example of what I'm working with

Balance

Current

90

[Code]....

View 2 Replies
View Related
May 30, 2007

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 11 Replies
View Related
Dec 2, 2006

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 5 Replies
View Related
Jul 6, 2007

Is there a way where i can vlookup a column and return all matches if there are multiple values?

View 3 Replies
View Related
Nov 29, 2013

I have attached an example workbook with a tiny subset of data and a number of criteria from the Dashboard Engine page removed.

What I need is to sum the total value for each division on the second sheet based on the date criteria (so for the first cell Jan 1/2010 - Jan 31/2010) and on a match between division name on the Dashboard Engine sheet table and the Masterdata sheet. I have tried a number of variations but keep getting a 0 for each return.

Sample Workbook.xlsx

View 12 Replies
View Related
May 31, 2014

What i'm trying to do is i would like to compile in 1 column all duplicate values from multiple cells.

ex. A1 to 10 is numbered 1 to 10 respectively, B1 to B10 is numbered 6 to 15 respectively. which means in A1:B10 the duplicate values are 6,7,8,9,10. i could like these number to show automatically in C1 to C5.

View 9 Replies
View Related
Apr 27, 2009

I am trying to add values

and I am having a problem when I introduce a blank lookup as in status here.

I have included the formula below ...

View 9 Replies
View Related
Jun 26, 2014

I am working on an attendance spreadsheet in Excel 2003 at work that will display data showing number of learners allocated to an activity, number unallocated, number attended, number not attended, number of acceptable reasons and number of unacceptable reasons by Block Letter. My problem is that for a number of reasons the same learner number is allocated to a number of activities each day. I need to know how many learners from each block have been allocated, not how many allocations each learner has from each Block.

I have tried different formulas, but fast running out of time

I have attached a modified sample from the main spreadsheet

I need : Cell H4 to calculate the number of unique values from Column C on Mon tab but only meeting the following criteria Mon!$N$3:$N$4000="AM", Mon!$O$3:$O$4000=D4, Mon!$B$3:$B$4000="mon1" I can then apply to the other rows.

View 7 Replies
View Related
Oct 13, 2006

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 4 Replies
View Related
Jan 30, 2008

in column a I have dates and in column b I have names.

eg

A1 = 1/1/08

A2 = 2/3/08

A3 = 3/1/08

A4 = 3/1/08

B1 = Jenny

B2 = Jenny

B3 = Jenny

B4 = Pat

I am trying to count the number of instances of "Jenny" in January.

I tried =sumproduct(A:A,>=39448

View 9 Replies
View Related
Jun 15, 2009

i have information across three columns the first has user-names in each row the whole way down, the second has between 1-7 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 7 Replies
View Related
Apr 10, 2014

I'm trying to use SUMPRODUCT as a sort of SUMIF/VLOOKUP hybrid. The following formula:

Code:

=ROUND(SUMPRODUCT('[12 GB BUDGET 15 V1 LOCAL VERSION.xlsx]

Revenue'!$G$3:$FM$3=$K2)*('[12 GB BUDGET 15 V1

LOCAL VERSION.xlsx]Revenue'!$G$27:$FM$27);0)

... should check for the value in cell K2 of the current sheet, compare it to all the values in row 3 of the Revenue sheet, and add up any and all values for those columns in row 27 where there's a match in row 3.

I don't get an error, but it's not adding anything up, either.

View 9 Replies
View Related
Jan 21, 2004

Is there a way to sum a list that contains both text and values using the SUMPRODUCT function? My efforts yielded the #VALUE! error. SUM and SUMIF will ignore the text but I have multiple criteria.

View 9 Replies
View Related
Aug 5, 2006

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 7 Replies
View Related
Jan 19, 2014

I am trying to sum up column V, when for a specific date in column z, and only sum when the two names EEM, Merri1* are involved. I used the * because there are a couple of variations for MErri1 after the 1. The formula is not working. I believe it's the way I'm combining this

[Code] .....

Full formula is below:

[Code] .....

View 14 Replies
View Related
Apr 14, 2009

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 3 Replies
View Related
Jun 9, 2009

fix cell E8-E19 (totals). I don't think its anything to do with the date format.

View 2 Replies
View Related
Oct 21, 2009

I'm working on a spreadsheet to rank stores based on how they perform in certain metrics. These metrics are weighted, and occasionally a metric for a store will get waived. I'm having trouble figuring out how to handle this without making a custom formula for each occurrence.

View 7 Replies
View Related
Mar 1, 2013

Is there a simple way to include the values of an entire range?

This is what I tried, but I get #name

Code:

=SUMPRODUCT((Datasheet!J2:J65000 = Range("C3:AA3"))*Datasheet!F2:F65000)

If I use a single cell it works, but only with what matches that cell.

Code:

=SUMPRODUCT((Datasheet!J2:J65000 = C3)*Datasheet!F2:F65000)

I'd like the criteria to match anything in that range C3:AA3. Do I need to specify each cell individually or is there an easy way have it use the entire range's values for its criteria?

View 9 Replies
View Related
Jul 30, 2008

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 wildcard-type (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 9 Replies
View Related
Jul 8, 2014

I have 6 worksheets in my file. In sheets 1-5, column A2:A26 list people's names. Some people's names appears on more than one sheet. Not all cells are populated with a value.

ex.

SHEET1

COLUMNA

Bill

[Code]....

My attempt was... =INDEX('Week1:Week5-!$A$2:$A$26,MATCH(0,COUNTIF($A$1:A1,"Week1:Week5"!$A$2:$A$26),0))

where the sheets were Week1-Week5 and the values on each sheet was A2:A26. But I think there's an issue with Excel being able to 3D reference for these types of functions.

View 3 Replies
View Related
Jan 21, 2014

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 Related
Jul 17, 2009

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 10 Replies
View Related