# 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)
Sell119,428null
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.

## Excel 2010 :: Sumproduct With Multiple Criteria?

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

## Excel 2010 :: Sumproduct Of Unique Values In Multiple Ranges

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.

## Excel 2010 :: Average Ignoring Zero Values / Non-Consecutive Cells

Dec 4, 2013

1.jpg

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

## Excel 2010 :: Pulling Data Based On Multiple Criteria With Duplicate Values

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

## Conditional Formatting Multiple Number Values But IGNORING Text?

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.

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

## Multiple Criteria And SUMPRODUCT (count The Number Of Rows That Have Values Greater Than 10/01/2008 In Either Of Two Fields)

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.

## Excel 2010 :: Counting Cells With Multiple Criteria On Multiple Sheets In Workbook

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.

## Excel 2010 :: Lookup Multiple Criteria Across Multiple Sheets?

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

## Excel 2010 :: Conditional Formatting With Multiple Criteria?

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.

## Excel 2010 :: Conditional Formatting Ignoring Cells With No Data?

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.

## Excel 2010 :: SUMIF With Multiple Criteria - How To Add INDIRECT To Formula

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.

## Excel 2010 :: Conditional Format Based On Multiple Criteria

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

## Excel 2010 :: Highlighting Rows In VBA Based On Multiple Criteria And Sum Function

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

## MATCH For Multiple Criteria While Ignoring Empty Array Cells

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.

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:

## Excel 2003 :: Formula For Counting Values Across A Range Using Multiple Criteria Across Multiple Sheets

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.

## Excel 2010 :: Looking Up One Value And Need To Return Multiple Values Horizontally

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

## Excel 2010 :: Countif And VLookup Of Multiple Values

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‎

## Excel 2010 :: Showing Multiple Values Per Cell?

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.

## Excel 2010 :: Selecting Corresponding Values From Multiple Columns

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

## Excel 2010 :: Calculate Most Common Text Values By Groups

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?

## Excel 2010 :: Sorting Multiple Values In Ascending Order

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

## Excel 2010 :: Building Text File Based On Values Of Cell?

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.

## Excel 2010 :: Return Multiple Text Strings From Other Worksheet

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.

## Ignoring Specific Values While Selecting Multiple Cells

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.

## Concatenate Multiple Columns But Ignoring Blank Values

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?

## Excel 2010 :: If Statement With Text As Range Of Values And Numbers Needed As Output?

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.

## Excel 2010 :: Sumproduct And Min / Max Functions

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

## Excel 2003 :: Ignoring Null Values When Plotting Bar Chart?

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.