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 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 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'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]
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.
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.
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.
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)
I am using excel 2010.
I have a spreadsheet with the following:
Column E is a product. If that product is ordered, any character is entered in that cell
Column F has a due date
Column I has the received date
What I want is to count the number of cells that have any character in column E AND the received date is later than the due date
These two formulas are working fine alone but I cannot get them to work together.
=SUMPRODUCT(--(F:F<I:I))
=SUMPRODUCT(--ISTEXT(E2:E1000))
I have tried all kinds of tweeks to the following to no avail:
=SUMPRODUCT(--(F:F<I:I),--(ISTEXT(E2:E1000)))
I've not used SUMPRODUCT previously and can't understand how to get results for the attached.
I've tried SUMIFS but it doesn't work because I'm looking down columns and across rows, I'm assuming.
I've attached a summary of what I'm trying to achieve. I want to sum all costs with an R,P,I,G, etc. in column C for December '13 (E3) in the top table.
The second table is actually in a different sheet but is the source of the data I need added.
Sumproduct P&L.xlsx‎
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].....
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: ....
I have an array that contains order numbers, tracking numbers and shipment costs. I want to get the total value of the shipment cost per order. the problem is, there are some duplicate shipments (ie same tracking number) and I don't want to include those. I can't delete the duplicate entries from the database for reasons I won't go into here.
so I tired to use a formula like =SUMIFS(C:C,A:A,A1,B:B,B1)
A B C
11462046 CJ225083125US 10.51
11462051 CJ225082247US 17.04
11462046 CJ225083125US 10.51
11462046 CJ225083564US 22.40
the formula doesn't work (won't even let me enter it) but if it did, it should give a result of 32.91. it would add C1 and C4 (but not C3 because even though A3=A1, B# also equals B1 and that is what I don't want to add.
I think maybe a sumproduct formula is what is needed but the negative criteria is throwing me for a loop.
The part in green will count the number of entries for the name Johnson & Freedman LLC perfectly fine. However when i add the last part in red i receive a #Value! error.
Col. W is formatted as General and has a data validation for the user only to choose Pass or Fail.
Not sure why it isn't working.
Code:
=SUMPRODUCT(--( 'SCRA'!B26:B29>=Sheet3!C2),--('SCRA'!B26:B29
I have the following two columns in A1:B4 (customer # followed by percentage)
1 0.5
2 0.9
3 0.8
4 #DIV/0!
In column D i have a list of the customer #s. In column E i try to identify if the customer in column D have a percentage >=.8.
I am using the below formula, but getting a #DIV/0! error due to the error in cell B4, which i am not allowed to change using an iferror formula.
=SUMPRODUCT(--(A1:A4=D2),--(B1:B4>=0.8))
Is there a way to get around this using sumproduct or any other method to determine if the customer in D has a percentage >= 80%?
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 3 Replies View RelatedI 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
I want to compare 2 lists in separate sheets based on multiple criteria and delete the duplicates
Sheet 1 - new list in column A:E
Sheet 2 - old list in columns B:F
So here is what I need: the macro should generate single IDs made of Sheet 1 Ai,Bi,Ci,Di,Ei cells for each row i to the end of the list + generate single IDs made of Sheet 2 Bi,Ci,Di,Fi
If . Evaluate (Sumproduct (IDs made of Ai,Bi,Ci,Di,Ei from sheet1) & Sumproduct IDs made of Bi,Ci,Di,Ei,Fi from sheet2) >1 then delete the entire row in Sheet 2.
This will leave me with only updated items (rows) in Sheet 2
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.
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 RelatedI 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 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'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 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 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
