# Sumproduct With Multiple Criteria Using Non Numerical Values?

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
ADVERTISEMENT
Aug 28, 2009

I have the following 'numerical values'

2510000.011

3010000.011

3510000.011.3

2518000.032.5

3018000.043

3518000.043.7

2530000.125.5

3030000.145.8

3530000.176.2

With the first two values as criterias, I need to find the 3rd and 4th value

Example: If I have the criteria as 30 and 1800 , I should get the result as 0.04and 3

View 10 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
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
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
Mar 10, 2013

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

View 7 Replies
View Related
Feb 22, 2012

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

View 1 Replies
View Related
Jan 30, 2009

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 9 Replies
View Related
Apr 28, 2009

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 9 Replies
View Related
Jul 24, 2006

I need to expand my SUMPRODUCT to calculate a numerical value. I have set the formula to calculate how many medical leave, training and annual leave taken by my staff. Now, I need to expand the formula to calculate how many items that was completed on region1,region2 and region 3 as well as working hours. I tried adjusting the formula but it did not work. I have attached the simplified version of the file. The actual is as long as more than 10000 entries.

View 5 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 16, 2009

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 9 Replies
View Related
Oct 1, 2009

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 8 Replies
View Related
Jan 26, 2010

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 2 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
Aug 20, 2014

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

View 4 Replies
View Related
Jan 10, 2014

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â€Ž

View 11 Replies
View Related
Nov 8, 2007

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 14 Replies
View Related
Nov 12, 2011

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.

View 9 Replies
View Related
Aug 19, 2012

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

View 8 Replies
View Related
Jun 6, 2014

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%?

View 2 Replies
View Related
Nov 24, 2008

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 Related
Nov 3, 2009

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 4 Replies
View Related
Feb 27, 2014

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

View 1 Replies
View Related
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
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
Mar 18, 2013

I maintain the data flow at my work. We send and receive the data using excel files with specific formatting that I then upload to the database. Each time I send or receive the excel file I must log them, this is what my code question refers to.

I use RDBMerge to merge all the contents of the 100 plus excel files into one worksheet. The first part of the macro cleans up the merge data for use in the log (i have attached an example of the clean data and finished log).

The blue shaded area of the "Raw_Data" is what the clean data looks like, the yellow column is what current macro records for each record.

As you can see by the example the Raw_Data is only two files LL_LLL_BOB_ToLLLLL_20121228_01 & LL_LLL_BOB_ToLLLLL_20121230_01, each with more that one record.

The log code in column "H" Is based on this criteria:

First Letter of the Unique ID in column "E" - O, M, or L

Program Type in Column "F" - U or R

1. O-U = U

2. O-R = RU

3. M-U = U2

4. M-R = R2U

5. L-R = R

You will note that Columns G-R of the "Log Sheet" correspond to the "Record Type" found in Column "G" of the "Raw_Data" sheet.

This is the area where my skill at using scripting dictionaries fails.

The results for the log list each file only once, but the log code for each corresponding "Record Type" in columns G-R of the "Log Sheet" must contain each unique instance of the code. In other words

if LL_LLL_BOB_ToLLLLL_20121228_01 contains an O-U with an "A" Record Type and an M-R with an "A" Record Type; then, on the log sheet there needs to be the codes "U/R2U" in the cell intersection of the LL_LLL_BOB_ToLLLLL_20121228_01 record row and "A" column (which is column "G")

So, If the File contains one of each code for each Record Type the corresponding cell must house one of each code separated by a "/" without any spaces. This means the cell value could no code, or one code and all the variations in between to all five codes. Also, for ease of human reading the log codes should be concatenated in the 1-5 order that I listed them in (U/RU/U2/R2U/R)

Here is my code so far.

VB:

Option Explicit

Sub test()

Dim dic As Object, a, i As Long, rng As Range, e, w, n As Long

Set dic = CreateObject("Scripting.Dictionary")

[Code].....

View 1 Replies
View Related
Feb 9, 2014

I have saved this on a 2010 workbook as I am at home but this will be used on a 2003 workbook.

I have several projects on one spreadsheet which multiple users will be working and I am trying to create a summary sheet of the work carried out.

Each user is expected to carry out a task on each row of the data held in each worksheet (research, call, update etc) and each task (Option 1-5) is assigned a value. Each user is expected to meet a certain level of points per day to calculate productivity.

I am looking for a sumproduct along the lines of the summary sheet attached but mine just takes one sheet into consideration and I need one for all sheets.

View 12 Replies
View Related