# 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
ADVERTISEMENT
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
Jan 6, 2009

I have 4 columns, A B C and D. They are a mix of formats.

A= Name (text)

B= Date

C= Code (text)

D= Number

I need a formula that gives me the total from column D, provided conditions for columns A, B and C are met. The date condition is month only, so I'm using MONTH(1) for January etc.

I can find a formula if there are only three columns total (using SUMPRODUCT), but not 4.

View 14 Replies
View Related
Apr 10, 2008

My sumproduct has multiple conditions - is there a limit to the number of multiple conditions one sumproduct formula can have? I didn't think there was????

The formula looks like this, and should return results - at the moment, it returns #N/A. Does it have anything to do with the fact that I'm using named ranges?

=SUMPRODUCT(--(Data!W:W>=Cumulative!A12)*(Data!D:D=Super)*(Data!E:E=Region)*(Data!Q:Q=EWC)*(Data!J:J="H")*(Data!L:L="Tonnes"),Data!K2:K65536)

View 9 Replies
View Related
Jan 16, 2009

I am trying to get sumproduct to work on a table where two conditions in the same column must be true (ie two employees must both have worked on the same project) in order to calculate a result. Trouble is my formula doesn't produce anything but a big fat zero.

Column A contains the list of projects. Column B has the list of employees. And column C has each employee's cost. So:

=sumproduct((column A = project1) * (column B = Joe) * (column B = Mark) * (column C))

=total cost of Project1 when both Joe AND Mark work on it.

Unfortunately, when I structure sumproduct this way, it returns zero.

View 9 Replies
View Related
Feb 8, 2009

I have a few varieties of SumProduct-formulae that are very helpful when dealing with numbers and/or letters in set conditions.

Like this one, which counts rows where conditions are met in four columns:

=SUMPRODUCT(--(Z413:Z432="G");--(AB413:AB432<-3);--(AC413:AC432>0,4)*(AD413:AD432>5))

and this one, which sums the values of the same

=SUMPRODUCT(--(Z413:Z432="G");--(AB413:AB432<-3);--(AC413:AC432>0,4);--(AD413:AD432>5);(AD413:AD432))

How does one make these two do their respective jobs when one or some of the columns are conditions for the next column - with their changing values, and thereby changing conditions?

Instead of "bigger than 0,4" as the condition for the AC part, it should be something like "bigger than it's AD counterpart" (AD value on the same row).

I have a few combinations of these "internal" measurements to do (the AB in addition being < than a certain percentage of the AC, and so on) but first of all I need a solution to the placement of the > and < signs.

View 2 Replies
View Related
May 7, 2012

I need to average with multiple conditions. Is there a way for sumproduct to do such a thing? How to average with conditions?

View 3 Replies
View Related
Dec 6, 2009

Can a sumproduct formula accomodate multiple criteria?

The following is a sumproduct formula, for just one condition.

SUMPRODUCT(--(A1:A100="Red Sox"),--(B1:B100""))

View 9 Replies
View Related
Nov 17, 2007

see my attached sheet cotaining the following questions. in a day report sheet how should i count request matching the crateria of date and other conditions. in a monthly report a heavy conditional sum calculation which make slower sheets how can i make it faster.

View 2 Replies
View Related
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
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
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
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
Nov 1, 2009

i dont understand why this code is not working. i get run time 1004 application or object defined error. basically i want to count column 11 if there is a value in column 2.

Public Sub offloadDoor()

Dim unassigned As Long

unassigned = 0

For rowvar = 18 To 504

If IsEmpty(Sheet2. Cells(rowvar, 11).Value) = False Then

If IsEmpty(Sheet2.Cells(rowvar, 2).Value) = False Then

unassigned = unassigned + 1

End If

End If

Next

Sheet8.Cells("b3").Value = unassigned

End Sub

View 5 Replies
View Related
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
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
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
View Related
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
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
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
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
Aug 4, 2005

I have a table in Excel:

The first row is time in years.

The second row is method name,say,"A","B","C".

I want to count the number when the time is less than 5 years AND "A"

method is adopted.

I tried this:

count(if(AND(C2:Z2<5,C3:Z3="A"),C2:Z2) but it didn't work.

how to revise the formula?

In the mean time, count(if(C2:Z2<5,C2:Z2))worked as well as

countif(C2:Z2,"<5")

View 9 Replies
View Related
Jan 22, 2009

I have a worksheet with 3 columns in it. these are entitled "area", "uploaded" and "status". uploaded will be a numerical value and status will either be "awaiting signoff" or "completed" what i need to do is list all of the different areas and add the "uploaded" values together IF the status is completed.

View 4 Replies
View Related
Apr 13, 2007

What formula would I use to count a cell only if it met one of three conditions?

ie only if the cell equalled 1, 2 or 3 but not 4?

View 9 Replies
View Related
Feb 13, 2009

40,000 rows,

Column A is a Port Code . . . always 4 digits

Column B is a 2 digit code representing a mode of of transportation.

I did it the "brute force" way of concatenating the two columns into column C, then sorting and subtotalling column C . . . .

View 9 Replies
View Related
Mar 29, 2007

There are a variety of tabs on this database. These tabs track the large customers and specific brands.

In the “Sales Rep Calendar” tab, I have attempted to calculate how many quotes are established 1) per Month and 2) per OS Sales Rep for 2007.

Using formulas such as: ...

View 6 Replies
View Related
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
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
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
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