# Sumproduct Formula - How Do I Exclude Values

Apr 14, 2009

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

## Sumproduct Formula To Exclude String Values

Jan 22, 2014

Question is...in the below formula the red are pointing back to name ranges in a different tab on my spreadsheet. On that spreadsheet I also have a name range called DTCCSUBSUPPREF

I would like to add within my formula, a string which will point to that name range and exclude values if they fit my criteria. Basically I need to see values only where comp or tri dont show up. comp or tri could be the only value in the cell, or could be in the beginning of a string, at end, or in between. Below examples. I have tried (--(DTCCSUBSUPPREF<>"*comp*")*(--(DTCCSUBSUPPREF<>"*tri*") , and I would have thought the * characters would have served as a catch all.

Below is the entire forumla just in case.

11111111FT04comp
comp
tri
11comp
11tri
tri11111111FRP06

## Formula To Add Numbers And Exclude Text Values

Apr 1, 2009

I need a formula that calculates the values of 5 seperate cells located in different places throughout my sheet. The value will either be numerical or display a text value (normally "RD"). If it displays text I do not want that value counted. So it literally just totals the number values.

## Count Using Sumproduct (but Need To Exclude Certain Data)

Dec 4, 2013

I am trying to exclude unusable data from my analysis. I have flagged data in the flag column on sheet 1 by a "1". A "0" is good data. On sheet 2, I have counts for the amounts of a certain code per partner. I used a sumproduct formula for the counts, but now I want to exclude all data with a flag "1".

## SUMPRODUCT Exclude Numbers And Text Strings?

Apr 19, 2014

I'm trying to sum up the total of a list excluding a list like this:

Column B
012*
019*
1758*
70228
70229
70230
70241

In my worksheet 1, it contains e.g.
Column AC
012-something
012-text
019-jumbo
016-things
70228
70221

Column W contains the numbers I want to add up:
\$15
\$12
\$10
\$19
\$15
\$11

My current formula is:
=SUMPRODUCT(--ISNA(MATCH(Sheet1!AC\$4:\$AC\$157,B2:B7,0)),Sheet1!W4:W157)

It has managed to exclude the numbers (e.g. 70228, 70229, etc) but not the text strings (e.g. 012*) when summing it up

## Formula Not Working For Sumproduct When Only Looking Up 2 Values

Jan 19, 2014

I am trying to sum up column V, when for a specific date in column z, and only sum when the two names EEM, Merri1* are involved. I used the * because there are a couple of variations for MErri1 after the 1. The formula is not working. I believe it's the way I'm combining this

[Code] .....

Full formula is below:

[Code] .....

## SUMPRODUCT Formula Throwing Out #VALUES!

Jun 9, 2009

fix cell E8-E19 (totals). I don't think its anything to do with the date format.

## Sumproduct Formula: Multiply Monthly Values With A Maximum Value In Any One Period

Jun 16, 2009

Need the formula to multiply monthly values with a maximum value in any one period? The sample file attached explains it better.

## Add Additional Formula To Existing Formula To Exclude Weekends And Holidays?

Mar 27, 2014

I have an existing formula in a cell linking back to a start date only. determining how to add an additional formula to exclude weekends, and possibly holidays.

## Exclude Zero Values From Copy And Paste VBA

Aug 23, 2013

How do I adjust this formula so it copies & paste special values rather than copying formatting etc? I am very new to this and I have looked at other examples but have found it hard to adjust my code using those examples.

VB:
Sub PrepayjournalKW()
'
' PrepayjournalKW Macro
'
Range("A6", Range("A" & Rows.Count).End(xlUp)).Copy Destination:=Sheets("Journal").Range("A1")
Range("B6", Range("B" & Rows.Count).End(xlUp)).Copy Destination:=Sheets("Journal").Range("C1")
Range("AB6", Range("AB" & Rows.Count).End(xlUp)).Copy Destination:=Sheets("Journal").Range("D1")
Range("AF6", Range("AB" & Rows.Count).End(xlUp)).Copy Destination:=Sheets("Journal").Range("E1")
End Sub

## Exclude Minimum Two Values In Average

Dec 8, 2006

I have looked on here and I can find the minimum value in my range but what I am trying to do is find the minumum two values. Or rather, i want to find the average of the remaining values.

If the lowest value happens THREE OR MORE times, i still only want to exclude two of them. Same thing goes if the second lowest happens more than once, i only want to exclude at most two values from the avg.

currently, i have 12 values in my list.

example:

list --> one in each cell going across

25, 50, 87, 56, 99, 80, 81, 82, 50, 100, 98, 99

i have the answer as 83.2 doing it by hand. the 25 and one of the 50s would be dropped and the rest would be averaged.

## Average Of Last 5 Values But To Exclude Any Blank Cells

Jul 3, 2014

I have a scenario where I have a rolling list of sales figures which get added to each week that passes.

I need a formula that will calculate the last 5 weeks of sales and generate an average - which I think I have an idea how to do.

The sticking point is that so as not to skew the averages, when there has been an exceptionally busy or quiet day for a reason we know about I exclude the sales from that week.

This then interferes with the averages as it either takes it as a zero and lowers it or seems to stop formulas from working.

So to summarise:

Average of last 5 weeks sales
Excluding any blanks
Dynamic enough to always pick up the last 5 values in the list (i.e. the last 5 weeks)

## Formula To Exclude #div/0! From A Max Value

Aug 16, 2009

is there a formula to exclude #div/0! from a max value. say you have a column with a few #div/0! is there any way for the max not to be #div/0!

## How To Exclude Row From Array Which Has Formula In It

Jul 30, 2014

For example the formula below is in cell AU6. I want to exclude A6 from the array. Row AU7 would need to exclude A7 when I drag it down.

=MAX(IF(\$A\$6:\$A\$493=AT6,\$AS\$6:\$AS\$493))

## Formula SUM - Exclude Cells With Text / Value?

Feb 3, 2014

Is there a way to do a Formula (SUM) but exclude cells with invalid values? I would like to add up all values in attached spreadsheet for cells A14 to A28 and again for A49 to A63, as well as get an average C-14 to 28 and again for C49-63.

As some of the cells contain text rather than numbers, the formula doesn't work. how to exclude these cells?

## Exclude Weekends And Holidays From A Formula.

Aug 27, 2009

I have been asked to create a worksheet to calculate the percentage of the utilisation of a number of loan cars per month. Each of the cars may be used on each and every day of the week but the percentage utilisation is to be calculated on working days only. However it is essential to record the name of the driver of the car at weekends and holidays. Column A is populated with the dates of the month. Colum B is populated with the relative days of the week. In column C and subsequent columns I record the name of the driver of a particular car registration. Is there any way that I can count the number of drivers of each car on working days only and not weekends or bank holidays?

## COUNTIF Formula To Exclude Zeros

Aug 22, 2012

Names
Quarters
Nickels
Dimes
Pennies
Record exc 0s

John
0
1
2
3

[Code] ........

In my table above, I would like to use the countif formula to count records based on the person, but I don't want the count to include values if the sum of quarters, nickels, dimes, AND pennies equal 0.

## Exclude Zeros From Array Formula

Aug 23, 2008

I have 16 columns in a single row that I am trying to apply to an array formula. That works, but I need to exclude all cells with the number "0" from the array range. I think I can do this myself in VBA but I don't want to run a macro every time something changes. Is it possible to do this in excel?

{=10*log(10^(A1:A16/10))}

It is possible that A1:A4 is a non-zero range and A12:16 may be a non-zero range. (Zeros will always be grouped in 4s)

## Formula To Exclude Counting Of Text In A Cell

Jul 14, 2014

having the formula exclude counting text in cells.

=IFERROR(SUMPRODUCT(--(INDIRECT("'"&TEXT(B\$2,"mm-dd-yy")&"'!A4:A44")=Tracking!\$A3)*(INDIRECT("'"&TEXT(B\$2,"mm-dd-yy")&"'!D4:J44")>8)),"")

I would like it to only count numeric values over 8.0. Currently if I have text in a cell such as "PTO" it returns as a added value.

## Formula To Exclude Weekends (Saturday And Sunday)

Aug 4, 2014

I have below formula which gives me the due status.

[Code] ......

How can I modify the code so it excludes Saturday and Sunday.

## Concatenate Multiple Vlookup Results With Dash Exclude Null Values Or 0 Return?

Jun 10, 2014

The 3 vlookup will be in a single cell for concatenation like :

=vlookup1&"-"&vlookup2&"-"&vlookup3

Problem the are 3 vlookups where it will return names (with format -> Fname, Given Name Middle) and probably blank returns.

I have problem with the replace since there are also spaces between the Fname,Gname and Mname sample name with spaces.

Apr 27, 2009

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

## Sumproduct - Values Across Three Columns

Jun 15, 2009

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

## SUMPRODUCT Not Picking Up Values

Apr 10, 2014

I'm trying to use SUMPRODUCT as a sort of SUMIF/VLOOKUP hybrid. The following formula:

Code:
=ROUND(SUMPRODUCT('[12 GB BUDGET 15 V1 LOCAL VERSION.xlsx]
Revenue'!\$G\$3:\$FM\$3=\$K2)*('[12 GB BUDGET 15 V1
LOCAL VERSION.xlsx]Revenue'!\$G\$27:\$FM\$27);0)

... should check for the value in cell K2 of the current sheet, compare it to all the values in row 3 of the Revenue sheet, and add up any and all values for those columns in row 27 where there's a match in row 3.

I don't get an error, but it's not adding anything up, either.

## Sumproduct Using Text And Values

Jan 21, 2004

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.

## Count The Values Through Sumproduct In VBA

Aug 5, 2006

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

## Sumproduct, Excluding Some Values And Adjusting

Oct 21, 2009

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.

## SumProduct With Range Values As Criteria?

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?

## Sumproduct Multiple Values In One Column

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

## Sumproduct - Select All Values Per Wildcard

Jul 30, 2008

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