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

View 4 Replies


ADVERTISEMENT

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

View 3 Replies View Related

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

View 2 Replies View Related

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

=SUMPRODUCT((--(DTCCSUBTRANSTYPE<>$G$2))*(--(DTCCSUBEVENTTYPE<>$I$2)*(--(DTCCSUBBACKLOAD<>$I$3)
*(--(DTCCSUBINPUTSOURCE<>$I$4)*(--(TEXT(DTCCSUBTRADEDATE,"MMM-YY")=$D$2))))))

View 6 Replies View Related

Using Count Function Exclude Zeros In Count

Jul 18, 2007

I am trying to count data using several criteria

1.Need to add data from for a certain category, say "blue" + data during a certain date but exclue the ones with zeros

My formula using arrays look like this, but it is still counting data with zero in the cell as an item

=count(if(A4:A400(text by category)=”blue”,count(if(O4:O400(date)<”04/01/07”,count(An4:An400)[Actual data],-(countif(An4:An400,0))))))

View 9 Replies View Related

Using SUMPRODUCT To Get Count

Dec 5, 2013

I'm trying to get the count of all cells in column F of one workbook that have a value of "RES" or "RET" and put that count in cell B2 of another workbook. At first I tried COUNTIF but quickly realized that it doesn't work when the other workbook is closed. I tried this, but it gives a REF! error:

Code:
=SUMPRODUCT(('H:Benefit BillingTurnover Reports2011[Turnover - termed thru 1-31-11.xls]site only'!F:F
="RES")*('H:Benefit BillingTurnover Reports2011[Turnover - termed thru 1-31-11.xls]site only'!F:F="RET"))

The workbook and worksheet referred to definitely exist in the directory in the path, and the F column has a mix of values including the two I'm looking for. Once I have a working formula, it will ultimately be populated into a large range of cells by a VBA sub (that part I can do ).

View 5 Replies View Related

Sumproduct To Count From 2 Conditions...

Jan 7, 2009

I'd like to use a sumproduct function to count 2 conditions. I want to add the number of times the number 0 is entered in Column D when a 1 is entered in the same row within Column C next to it.

I'm using the formula below yet its wrong.... it gives the answer of 7 rather than 1 (see data in attached file).


=SUMPRODUCT((C3:C124=1)*(D3:D124=0))

View 2 Replies View Related

Count The Months Within The SUMPRODUCT

May 15, 2009

The below formula is a small piece of a formula but it's where it's causing the error to occur. When I'm trying to count the months within the SUMPRODUCT formula it works fine, but when there is a "TOTAL" it's returning an error.

AB11/04/2009#VALUE!22/04/2009 33/04/2009 44/04/2009 51/05/2009 62/05/2009 73/05/2009 8TOTAL 95/04/2009

Spreadsheet FormulasCellFormulaB1=SUMPRODUCT(--(MONTH(A1:A9)=4))

View 9 Replies View Related

Sum And Count Using SumProduct Function

Jun 9, 2009

*ABC53TypeCost$Bought54Orange3Friday55Orange5Sunday56Apple4Friday57Orange5Friday58Orange4Sunday59Apple4Friday60Banana3Tuesday61Orange4Sunday Excel tables to the web >> Excel Jeanie HTML 4

Now, i want the Sum and Count of Orange which is bought on Friday

Answer Should Be
in D54 =8 and E54=2

View 9 Replies View Related

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()
Application.DisplayAlerts = False
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))..............

View 7 Replies View Related

Using Sumproduct To Count Across Multiple Sheets?

May 8, 2013

I am trying to use sumproduct to count across multiple sheets and so far have

=SUMPRODUCT((INDIRECT("'"&$Z$1:$Z$7&"'!$K$190:$K$220")>0),(INDIRECT("'"&$Z$1:$Z$7&"'!$D$190:$D$220")=B22))

This is my latest iteration and there have been about 50 so far, none of which I can get to work.

View 9 Replies View Related

Using SUMPRODUCT To Count Certain Letters In A Cell?

Jun 4, 2014

I am trying to use SUMPRODUCT on one of my excel files. the condition is that it will try to count all those cells that contain certain letters. i tried using this formula, =SUMPRODUCT(--('All Computers'!$A$2:$A$100000="USA*"),--('All Computers'!$E$2:$E$100000="June")), but I am getting a 0 value.

View 3 Replies View Related

Sumproduct - Count More Than One Criteria By Date

May 19, 2008

I have a document with the following data

1: NAME
2: DESCRIPTION
3: DATE "12/15/2007" format

I would like to know if anyone knows how I can perform the following. And please excuse me for not writing this very technically I am a bit lost.

if name = "JOHN" and description = "APPLE" count how many apples there are for john.

Now it gets complicated for me because the dates are as follows. Lets say it starts 1/1/2004 the next cell might be 1/3/2204, 1/6/2204, etc all the way to 2008. I would like to find a way to say

If date = day/month/year I would like to count the whole month instead of the individual days. am I making sence? Let me give you another example.

record reads "," represent cells

JOHN,1/2/2204,APPLE
JOHN,1/3/2204,ORANGE
JOHN,2/3/2004,APPLE

I would like to be able to come up with the following

JOHN had 1 APPLE in JAN 2004
JOHN had 1 ORANGE in JAN 2004

View 14 Replies View Related

SUMPRODUCT - Count Multiple Conditions

Feb 16, 2006

Ive started using the sumproduct function to count multiple conditions which is useful

howveer if i want to count those records in one column that meet a condition and those records in another column that meet anyone of a number of conditions how can i do that?

the only way i can think is like the below

=sumproduct(--((columnA=apple)*((ColumnB<>Red)*(columnB<>Yellow))))

Rather than having to eliminate red and yellow i would like to say is green or blue.

View 14 Replies View Related

Sumproduct Count Range Of Names

Nov 17, 2011

What is the formula for sumproduct to count a say column c for a range of names and as long it match column k for yes.

View 1 Replies View Related

Count Function Similar To Sumproduct?

Dec 12, 2011

Similar to SUMPRODUCT, do we have any function to count cells with diffrent critereas. Countifs does not work since the critereas are at different coulmn and rows.

View 1 Replies View Related

Sumproduct To Retreive The The Count Against Each Criteria

Sep 5, 2008

I'm after a SUMPRODUCT that will give me the number of times that F is in column B2:B1000 when non of the following is in column A2:A1000.

000
001
002
003
004
005
006

All the above are in text format.

Example below would return 3.......

View 9 Replies View Related

Exclude Non Mature Data

Nov 15, 2009

I have data that I calculate by month and at the bottom I calculate a weighted average. However, I want to exclude the last two months (data points) because they are not mature.

So every month when I update the spreadsheet, it would be the two newest data points to exclude.

View 7 Replies View Related

How To Exclude Certain Data Points

Jan 22, 2007

I'm running a query of 30 and 60 days worth of data but I don't want certain dates to be included in the queries. How do I exclude certain dates and at the same time pull in day 31 to include this in my 30 day high or average etc.?

For example:

1/19/2007 4.7
1/18/2007 4.5
1/17/2007 3.8
1/16/2007 4.9
1/15/2007 3.75
1/12/2007 3.75

I want to exclude 1/15 because it is a holiday and it just repeats the data from 1/12. So I want it to pull in the data for 1/12 if I were doing a 5 day query. As I said before I need to do a MAX, MIN, AVERAGE and STDEV of 30 and 60 days but my formulas are skewed because of the holidays.

View 9 Replies View Related

Inputbox To Exclude Data

May 16, 2008

I need to have an inputbox for users to enter multiple store numbers. What I need to do is actually filter out the store numbers that was entered into the inputbox
my code so far is:

'Inputbox to filter out new stores
Newstore = InputBox("Enter New Store Numbers seperated by a space", "Enter New Stores", "119 120")
x = Split(Newstore, " ")
For i = 0 To UBound(x)

Range("A1").CurrentRegion.AutoFilter Field:=2, Criteria1:=Array(x) _
, Operator:=xlFilterValues
Next i

View 9 Replies View Related

Sumproduct To Count Special Content From A Table

Jan 22, 2014

I use a sumproduct to count a special content from a table.

I have the country, a special key and the product.

I have different products like rismo 12, rismo 100, rismo 200.

But i want to count them all.

So i have the following function.

=+SUMPRODUCT(((ISNUMBER(FIND("rismo";E1:E1000))*1)*(C1:C1000=D2)))*((H1:H1000=60)+(H1:H1000=65)).

In the cells c are the countrys. In the cells h are the special keys. I am looking for the 60 and 65.

My result is always 0.

View 3 Replies View Related

SUMPRODUCT Formula (count How Many Of A Particular Type Of Entry There Are)

Oct 3, 2008

I have a range of data going accross columns, the entries include, U, T, and F1 to F11. I have a requirement to count how many of a particular type of entry there are, this needs to be done in groups, e.g, counting attendance bonuses which are included in entries, F5,F6, F8 and F11, (i.e. in one cell i need to total how many F5's,6's,8's & 11's are in that row?!). i just cant quite seem to get the formula right to count them.

View 5 Replies View Related

SUMPRODUCT Multiple Ands/ors (count Of How Many Rows)

Jan 26, 2009

I'm having trouble with SUMPRODUCT. I would like a count of how many rows where:

Column A = PP
and
Column B = QQ or RR or SS
and
Column C = TT or UU or VV

View 2 Replies View Related

Sumproduct: Count Number Of Leaves Within A Specified Period

Nov 5, 2009

=SUMPRODUCT(SLVL!$B$6:$B$49>=$A$2)*(SLVL!$B$6:$B$49<=$B$2)*(SLVL!$A$6:$A$49=A12)

I'm sure that there had been an error somewhere but I can't figure it out. I'm getting zero value. Situation: need to count number of leaves within a specified period, thus I opted to use Sumproduct.

View 4 Replies View Related

Countif Or Sumproduct Function (count In A Range)

May 6, 2009

I am trying to do a basic count in a range, however am a little brain fried and its not working. On attachment, column D is to list all times out of column A and B listing only unique entries (have tried to use Macro with advanced filter however not liking it being an extract). Column E is to be a count of how many times the break time in Column D appears.

View 2 Replies View Related

Sumproduct To Count Response Between Time Range

Mar 20, 2014

I need a sumproduct to count response times in between 1-2 hours. The response times are calculated from an 'initial referral date and time' and a 'actual response date and time' (the difference between refererral and response time be across several days) and are in the format below:

Time between intital and contact

00:22:37

00:00:01

00:08:46

I have tried several different formula, but I can't get excel to recognise the time range I need. Its just the bit in red i need sorted.

=SUMPRODUCT((MONTH(Referral!$H$2:$H$1048576)=4)*(Referral!$E$2:$E$1048576="Initial")*(Referral!J2:J1048576>1)*(Referral!J2:J1048576

View 3 Replies View Related

SUMPRODUCT - Counting Records - How Do I Not Count Dupes

Oct 19, 2007

I'm using the following formula to get a count of records that have a matching function name that is in B10.

=SUMPRODUCT(--('Oct-2007_FunctionSurvey'!$T$2:$T$1498=B10))

My Problem: There are duplicate entries in my recordset based on COl A (AppID).

How can I change the formula to only count the occurences of the B10 values based on a unique AppID (col A)?

View 9 Replies View Related

Sumproduct Month And Year - Count Dates From A List

May 11, 2013

I am trying to count dates from a list using sum product (I found the formula via google) I have plugged it into my spreadsheet but it does not seem to be calculating correctly.

I have attached the spreadsheet too : sumproduct_Error.xlsx

=SUMPRODUCT(YEAR(Tbl_finish[Finish]=2008)*(MONTH(Tbl_finish[Finish]=1)))

I am expecting a count of all the dates in January 2008 with the formula above.

View 5 Replies View Related

Sumproduct With OR Logic - Count Number Of Rows In Dataset

Jan 17, 2014

I'm trying to count the number of rows in a dataset where column A is something, B is something, C is something, D is not something, D is not something else, and column E > 0 OR F is something but I just can't get the result I want.

My actual dataset is a staggering 294,000 rows and 46 columns so here's a simplified version:

View 8 Replies View Related

Sumproduct Formula: Count The Components Used To Make One Product

Oct 7, 2008

Is there a simpler way to to the calculations on the spreedsheet i have attached?

Basically I want to count up the components used to make one product

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved