Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










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="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 Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Extract Numbers Then Sumproduct Values Against Another Column Of Data
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 Replies!   View Related
Multiple Criteria And SUMPRODUCT (count The Number Of Rows That Have Values Greater Than 10/01/2008 In Either Of Two Fields)
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 Replies!   View Related
Lookup Multiple Values In Same Column With Same Column Heading
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 Replies!   View Related
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
Matching Multiple Repeating Values In One Column With Another
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 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
Vlookup A Column To Return Multiple Values
Is there a way where i can vlookup a column and return all matches if there are multiple values?

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 -in Column A I Have Dates And In Column B I Have Names
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 Replies!   View Related
Sumproduct- To Add Values
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 Replies!   View Related
Sumproduct - Values Across Three Columns
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 Replies!   View Related
Sumproduct Using Text And Values
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 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 With Multiple Criteria
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]

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 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 Replies!   View Related
Sumproduct, Excluding Some Values And Adjusting
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 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 Formula Throwing Out #VALUES!
fix cell E8-E19 (totals). I don't think its anything to do with the date format.

View Replies!   View Related
SUMPRODUCT Formula - Multiple Conditions?
Can a sumproduct formula accomodate multiple criteria?

The following is a sumproduct formula, for just one condition.

SUMPRODUCT(--(A1:A100="Red Sox"),--(B1:B100""))

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

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
Sumproduct :: Sum Data Based On Multiple Criteria..
I am trying to sum data based on multiple criteria..

The english version of the formula is Sum all refunds for Store during week

Original Data Format: ....

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 Multiple Ands/ors (count Of How Many Rows)
I'm having trouble with SUMPRODUCT. I would like a count of how many rows where:

Column A = PP
and
Column B = QQ or RR or SS
and
Column C = TT or UU or VV

View Replies!   View Related
Sumproduct For A Column With The Words YES, NO, MAYBE
How do I use a sumproduct for a column with the words "YES", "NO", or "MAYBE" appearing?

I'm using
sumproduct(--$C$1:$C$50000="YES"),--($C$1:$C$50000="NO"),--($C$1:$C$50000="MAYBE"))>1

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
Sumproduct For Lookup Row And Column
I use the sumproduct for the attached example. I know I have seen this somewhere on the forum where I can get a value based on a criteria from a row and criteria from a column, but I just can't seem to figure it out right now.

View Replies!   View Related
Sumproduct The Rows Where The Value In Column Is Not Zero
=SUMPRODUCT(C1:C3,1/(D1:D3))

Trouble is sometimes the value in D1:D3 could be 0 or nothing. how do I get the formula to only sumproduct the rows where the value in Column D is not a 0?

I tried the following

=SUMPRODUCT((D1:D3<>0)*C1:C3,1/(D1:D3))

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 Multiple Daily Transactions By Date And Month
Can someone tell me what I'm doing wrong for the weekly sums in this spreadsheet? The monthly sums work fine.

PS I can't use pivot tables. This spreadsheet is a quite small part of a more expansive set of worksheets, from which I am pulling data.

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(('Invoice-Detail'!J2:J50="NewJob_Post.NET")*('Invoice-Detail'!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
Sumproduct Formula: Multiply Monthly Values With A Maximum Value In Any One Period
Need the formula to multiply monthly values with a maximum value in any one period? The sample file attached explains it better.

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
Copyright © 2005-08 www.BigResource.com, All rights reserved