# Excel 2010 :: Sumproduct With Multiple Criteria And Ignoring Text Values

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
ADVERTISEMENT
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
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
Dec 4, 2013

1.jpg

I wan to calculate the average for three cells not in Sequence using AVERAGEIF with condition (VALUE >0)

View 5 Replies
View Related
Mar 14, 2012

I have a sheet (see Sheet 1) from a report we run which lists the following information: Personnel Number, Amount, Wage Type. This is generated for 1000's of employees, with each personnel number being repeated several times in column A.

I am trying to pull specific data to another sheet (see Sheet 2), which would ideally generate the sum of "Amount' for a specific wage type for each personnel number. The issue is is that there may be dplicates of the wage type for each ID number (which is also repeated).

For example, the total salary amount on sheet 2 for ID#12345678 would be 0, while for #9876543 it would be 1250. Is there a formula I could use on sheet 2 column B that would generate this?

Excel 2010 ABC1Personnel NumberAmountWage Type212345678550Payment312345678400Overtime412345678300Overtime512345678250

Payment612345678750Vacation798765432800Salary898765432250

Payment998765432100Overtime1098765432450Salary1198765432300Overtime

Sheet1

Excel 2010 ABC1Personnel NumberTotal SalaryTotal Overtime212345678398765432

Sheet2

View 3 Replies
View Related
Sep 6, 2013

I have a spread sheet where there are multiple rows of price quotes from different suppliers.

Some suppliers have quoted, some however have not and there is a "#N/A" or "no bid".

SupplierA

SupplierB

SupplierC

SupplierD

1.3

No Bid

2.3

No Bid

How can I get a conditional format to check each row for the lowest numeric bid, highlight it but ignore the text?? What I have tried so far doesn't work.

=G31=MAX($G$31:$J$31)

This does not seem to work, I don't know how to exclude the text values from the MAX function.

View 14 Replies
View Related
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
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
Aug 5, 2012

I am using MS Office 2010. I want to count---on multiple sheets---the number of times that a given cell is greater than another cell if and only if a third cell is equal to a given value. I want to do this for 4 sets of data on each sheet. I thought I had it figured out with this formula---

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$R1"),2*(AND("'"&$H$1:$H$43&"'!$E1">"'"&$H$1:$H$43&"'!$F1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$S1"),2*(AND("'"&$H$1:$H$43&"'!$G1">"'"&$H$1:$H$43&"'!$H1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$T1"),2*(AND("'"&$H$1:$H$43&"'!$I1">"'"&$H$1:$H$43&"'!$J1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$U1"),2*(AND("'"&$H$1:$H$43&"'!$K1">"'"&$H$1:$H$43&"'!$L1"))))

but it returns a value of zero each time. Clearly there is an error in the formula.

Here is some background:

-- $H$1:$H$43 is a block of cells that has the names of the sheets in the workbook

-- E1 and F1, G1 and H1, I1 and J1, K1 and L1 are the four groups of cells that I am comparing.

In the entire workbook, I want to add 1 (counting function) only when:

R1=2 AND E1>F1 or

S1=2 AND G1>H1 or

T1=2 AND I1>J1

U1=2 and K1>L1

on each appropriate sheet in the workbook.

View 1 Replies
View Related
May 28, 2014

I have a Excel 2010 workbook used to rota in a large amount of staff for a call centre, which is split into four teams. Each sheet corresponds to a month of the calendar year eg Jan201, Feb 2014 etc..

What im trying to do is put in a sheet at the front of the workbook that I can select the team, which populates the list of staff in that team and then checking across a specified date range gives the shifts that those respective staff will be working for the set time period (probably be looking at a seven day period and a 1 month period). (This in turn will be printed out to give to the staff members.)

View 2 Replies
View Related
May 16, 2013

I am trying to do a conditional formatting based on the result in one cell but it needs to add up multiple cells first to determine which conditional format it needs to use. I am not sure whether I need to do a formula conditional format or whether to use cell is greater than with =sum() in it. I have tried both but neither seem to be working correctly. I have attached an example of the spreadsheet and the conditions I am trying. I am using 2010 version.

Basically, the closing amount for Monday in prod 1 (cell c4), needs to display in red if sum of the cumm value for Tuesday (L4), Wed (N4), Thur (P4) & Fri (R4) is greater than that value or display in amber if the sum of the cumm value for Tuesday (L4), Wed (N4), Thur (P4).

I need to complete the same for the whole column for Monday (col C) for each appropriate product. Then the same for column B but this will sum different cells but the principal will be the same.

View 3 Replies
View Related
Sep 27, 2013

I'm using Excel 2010.

I have created a spreadsheet with 3 month inspection schedules on using the formula below.

=DATE(YEAR(C50),MONTH(C50)+3,DAY(C50))

I then copy this in to a cell where I want the date to appear... and then in to another cell where I want a further 3 month date to appear. Obviously changing the cell reference.

I would like the formula to ignore the very first cell "C50" if there is no date inputted in this cell.

View 2 Replies
View Related
Aug 11, 2013

Excel - 2010

I have the following formula:

=SUMIFS(FEB!TRANS_Cost,FEB!TRANS_Cat1,CAT_Main3,FEB!TRANS_Cat2,"Computer Items",FEB!TRANS_Status,"Paid")

TRANS_Cost = B:B on FEB tab

TRANS_Cat1 = C:C on FEB tab

TRANS_Cat2 = D:D on FEB tab

CAT_Main3 = "Everyday_Expenses"

TRANS_Status = E:E

In short the formula adds the total cost of anything 'tagged' as Everyday_expenses, computer items, paid. I want to adapt it so that it takes the value from a defined cell such as Month, so that data can be pulled by month.

View 2 Replies
View Related
Apr 25, 2014

I would like to shade my cell yellow if the priority equals A or A-NEW and ACV=o.

In the data below, items 4 and 13 would be shaded yellow.

I am using Excel 2010.

Priority A, B, C

Consumer UPC

Item Description

ACV Weighted Distribution

[Code] .......

View 8 Replies
View Related
Apr 25, 2014

I am trying to write a macro in VBA excel 2010 that compares 2 sheets.

The macro should be something along the lines of if column 7 on sheet 1 = column 1 on sheet 2

AND

on that same row if column 6 on sheet 1 = column 3 on sheet 2

highlight green

** also on sheet1 there can be the same batch ID so if it is the same batch ID it needs to calculate the sum and look at that amount...

Sheet1:

settleid

min Tran Date

Payment Vehicle

total Deposit

total CF

total MRI

RMBATCHID

475-T

03/03/2014

Connect

$562.95

$19.95

$543.00

6G000001450835

[Code] .......

Results >

Sheet1:

settleid

min Tran Date

Payment Vehicle

total Deposit

total CF

total MRI

RMBATCHID

475-T

03/03/2014

Connect

$562.95

$19.95

$543.00

6G000001450835

[Code] ..........

View 9 Replies
View Related
Nov 11, 2009

I'm working with a large amount of data (A21:BZ1503) and I'm trying to identify unique situations where any pre-defined combination of multiple columns in one row is flagged by producing a pre-defined value. For example:

I have my pre-defined criteria in worksheet 'X' hidden in my workbook -- note that there are many blank cells.

Worksheet 'X'ABCDE1Dept.CourseInst.Adj.2AGSM1.23MATH101Professor1.44ENGL1051.65ENGLProfessor1.86ETST1002

On worksheet 'A' the various users enter data -- each row is a unique group with data entered into the columns -- again note a cell can be blank:

Worksheet 'A'ABCDE1Dept.CourseInst.Adj.2AGSM110Professor3ETST2204ENGL108Professor5ENGL105Lecturer6MATH101Lecturer..............................

View 9 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
Mar 28, 2014

I have in column A duplicate values and in column B different responses (Sheet: Lookup). I need to look up the value in column A (Sheet: Results) and bring back all the responses in column B (horizontally).

Nittie Query.xlsx

View 5 Replies
View Related
Jan 15, 2014

I am working with an Excel 2010 workbook that has two worksheets in it. What I am trying to accomplish is I want the second worksheet to scan the first worksheet for a student's name, and count all of the instances that the student has a score less than a certain threshold (we'll say "5" for this example). I have tried using various combinations of vlookup and countif functions, but have not had much success. I did get it to a point where it worked, but only for the first instance of that student's name; it wouldn't continue searching the first worksheet for any other instances.

I have attached a sample workbook as a reference : Sheet1.xlsx

View 8 Replies
View Related
Jul 31, 2014

When I update a cell (change A1 from 2 to 3), any cell that references that cell correctly changes its value (B1 = 2*A1). However, the screen will show the new value in B1 (6) over the previous value (4). At first I thought it almost looked like a strike-through, but then I realized the old value and new value were simply stacked in B1.

If I scroll the screen away that cell and go back to it, the correct value will now show without the stacked values. I'm not having this issue in any other program (Open Office), and I don't seem to be having any kind of stacking issue in any other Microsoft program.

View 5 Replies
View Related
Feb 18, 2014

I have a spreadsheet with two date/time columns 'Date1' & 'Date2'. Each date/time column has its own column with corresponding values ('Var1' & 'Var2'). These dates cover the same time period, however values for 'Var2' were collected less frequently than 'Var1'. I want ONLY the values in 'Var1' that correspond to the dates in 'Date2'

I am trying to select values from one column 'Var1' which have correlating date/time in column 'Date1' that match the dates specified in 'Date2'. Basically I need the values from 'Var1' that match the same date/time as 'Var2'

See the attached image to make it clearer..

Excel2010

Excelhelp.jpg

View 3 Replies
View Related
Mar 14, 2013

I want to calculate the most common text value for groups. I have a table with a lot of records like an example below.

ID Code

1 HB

1 KU

1 KS

1 LV

1 KS

2 LM

2 KS

[Code]....

I want a table like below:

ID Code

1 KU

2 KS

3 KS

How can I do it in Excel 2010?

View 9 Replies
View Related
Dec 29, 2012

I am trying to sort multiple values in ascending order (example attached) however I can't get this to work. Tried looking at a few different forums and although there is plenty of sorting questions out there they all tend to be relating to dates not different values (i.e text and numbers).

View 14 Replies
View Related
Feb 26, 2014

I'm trying to use Excel to build a text file based on values from certain cells in Excel 2010. This is based partially off of static text that never changes and variables that will need to change. For example, I want Excel to output a text file that has the following text: The red fox jumped over the $X twice. I would want $X to be replaced by the value of A1 of the active worksheet. I will also have several lines like that, so it won't just be a single line, but anywhere from 20 - 120.

View 1 Replies
View Related
Mar 7, 2014

I need a formula or array to perform the following in Excel 2010:

I have 2 worksheets "Master Report" and "Free text comments". The master report will be shaed with individuals but the Free Text comments worksheet will not.

sample feedback report.xlsx

In cells D2 downwards on the "Master Report" I want to show the individual feedback comments from the "Free Text comments" worksheet that have been made by other people for the individual named in cell A2 on the "Master Report" worksheet. So on James' master report it would show the 8 individual comments listed for him on the Free text comments" worksheet.

I have searched the internet and tried various combos of lookups, IFs, index and match but have not come across the right formula yet.

View 1 Replies
View Related
Dec 20, 2012

I have to correct a spreadsheet that takes a bunch of values and creates an average.

=average(N15;N29;N43;N57;N71;N85;N99;N113;N127;N141;N155;N169;N183;

N197;N211;N225;N241;N255;N269;N283;N301;N315;N329;N343;N357)*100%

I didn't create this. Anyway, the problem is that most of these cells will be empty unless a whole bunch of other stuff is filled. These N cells take several values, put them together and create a new one. As time goes by they are filled. While they aren't filled, they'll return the DIV/0 error.

So when that formula I pasted there uses all of these values, it also returns a DIV/0 error because it is getting info from cells that have this error.

I managed to correct that with this

=averageif(N15:N357;"<>#DIV/0!")*100%

It works like a charm save for one problem. When I use averageif I'm forced to use an interval. Along this interval other values show up (it is always a value ranging from 0 to 2). So when the formula works its magic the results are slightly skewed because of this other value that I don't want.

So I figure there must be two ways around this. The first one would be using something like averageif that lets me use several handpicked cells instead of an interval. If I try

=averageif(N15;N29;N43;N57;N71;N85;N99;N113;N127;N141;N155;N169;N183;

N197;N211;N225;N241;N255;N269;N283;N301;N315;N329;N343;N357;"<>#DIV/0!")*100%

It doesn't work, it says I have too many values. So if I could the exact same thing as I did with averageif but keeping all of these values it'd be super nice.

The other solution, less nice but equally effective I guess, is using this same interval but having more than one criteria. The first criteria would still be the one telling it to ignore DIV/0 error, the second criteria would be the one to ignore any values equal or lower than 2.

View 5 Replies
View Related
Aug 13, 2012

I wish to Concatenate contents of several columns into a separate column, i tried using the following "column1 & column2 & column3 etc..." however with this the blank cells get concatenated too...What should is use so that contents get concatenated but blank cells are ignored?

View 4 Replies
View Related
Apr 23, 2013

Excel 2010, I have the following list (showing part of it):

Name

T-Shirt Sizes

Nathan

S

[Code]....

The aim of the list is to see what Sizes each and every person needs and then count how many T-Shirts are required for that particular size. The list is over a 100 entries long so counting it manually isn't really an option as it's time consuming as well as errors might arise.

Thus, I've been trying to use the IF statement with the following logic but to as no avail

IF Size in Column is XS

Add 1 to a particular Cell

IF Size in Colum is S

Add 1 to a particular Different Cell

and so on for the other sizes...kind of like a counter for a for-do-loop.

View 2 Replies
View Related
Mar 5, 2012

How to add Sumproduct and Min/Max functions? Using Excel 2010

I am looking for the minimum, or maximum number within a range while using the Sumproduct function.

=SUMPRODUCT(--ISNUMBER(FIND("R",CMSB3003.xls!$A$12:$A$20000)),--ISNUMBER(FIND("Reg",CMSB3003.xls!$AF$12:$AF$20000)),

--(CMSB3003.xls!$B$12:$B$20000>=$B$2),--(CMSB3003.xls!$B$12:$B$20000

View 7 Replies
View Related
Jan 21, 2014

I have some data that I'm plotting on a bar chart and I'm trying to "HIDE" the columns with zero or null values. Basically, if the column is blank, I don't want a "gap" on the chart. I'm not getting this to work.

View 6 Replies
View Related