# SUMPRODUCT With Multiple Criteria: Count The Number Of Documents

Nov 3, 2009
I have attached a spreadsheet with a small indicative data set to assist in understanding. I am trying to count the number of documents each individual has assigned to them that are not yet 'completed' (ie REGISTERED, IN WORK, REVIEWED). The problem I am trying to overcome is that the document state can be 1 of several values indicated in the same column.

I have tried using this SUMPRODUCT formula:

=SUMPRODUCT((($E$2:$E$11="REGISTERED")+($E$2:$E$11="IN WORK")+($E$2:$E$11="REVIEWED")*($B$2:$B$11="Jones")))

but it is generating incorrect values!

Specifically:

- Jones shoulld return 1

- Franks should return 3

- Smith shoudl return 0

View 4 Replies
ADVERTISEMENT
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
Oct 22, 2011

I have a macro that prints all documents that are found in a directory, i was thinking that it should be possible to modify this macro to count both number of documents as well as the pages that are printed, and display the count once the documents are sent for printing.

The Code:

Start of Code:

Sub ListDocNamesInFolder()

Dim sMyDir As String

Dim sDocName As String

sMyDir = "C:My Documents"

sDocName = Dir(sMyDir & "*.DOC")

[Code] .......

View 2 Replies
View Related
Feb 5, 2008

I have attached a small copy of the worksheet.

I need to count when the following set of 3 criteria criteria are met:

1: The Assessor Initials = DS

2: the month = Mar

3: Check 4 Ranges = columns I, K, M, O

I've tried using sumproduct (sucessful for other data) but not able to get it to work with dates.

View 9 Replies
View Related
Feb 21, 2014

I have come up with 5 countif formulas that work perfectly separately but need them to be combines into one big criteria.

=countif(PM[ProjectAge],">=200"

=countif(PM[Customer Name],"*" & purch & "*"

AND so on for each criteria. H

View 7 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
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
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
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
Jun 29, 2009

I have a formula which I have copied from one of your good selves and tried to adapt it. The formula is below. I a am getting #NA as my answer.

=SUMPRODUCT(ISNUMBER(FIND({"h","H"},B11:G11))*{0.5,1})

I have a person name in Colomn A and then 1 week B-G on a row and want to count number of days holiday.

View 4 Replies
View Related
Jan 21, 2014

I am looking around any way wherein I can sumproduct the values as given in attached sheet, basically I wanted to know the total MRP value of Sale and Stock

View 3 Replies
View Related
Jul 17, 2009

I am stuck - I have a large amount of data for a group of physicians I work for. I am trying to set up a monthly trend report to be able to run quickly after I plug in the data. I want to use some sort of lookup to look up two things - 1) the physician's specialty and 2) the month.

Can anyone look at the attached example and tell me how to do this? I have started a SUMPRODUCT formula, but am stuck on how to tell it to find only that month's data.

View 10 Replies
View Related
Mar 10, 2013

ID, Name, Point, Session

1111, Viking, 5, 1

2222, John, 6, 1

1111, Viking, 10, 2

What's the formula to get the Point cell value with criteria ID = "1111" and "Session = 2" ? In this case, it will return me the value of Point = 10

View 7 Replies
View Related
Feb 22, 2012

I was wondering if I could use a range of cells as my criteria as opposed to inserting quotation marks with each criteria. For example in the syntax below can I do something like this? Report!$C$3:$C$5000=B45:B51?

=SUMPRODUCT((Report!$A$3:$A$5000="XXX")*(Report!$C$3:$C$5000=???)*(Report!$E$3:$E$5000))

View 1 Replies
View Related
Jan 30, 2009

I'm trying to create a budget worksheet that pulls actual data from another sheet within the file for comparison (Budget vs. Actual). There are two criteria: 1) the actual transaction falls into the same category of transaction as the budget line item (e.g., mortgage payment) and 2) the date of the actual transaction matches the month in the budget (e.g., a January or March transaction isn't pulled into the actual data for February budget information). From there, I'd like it to sum any charges or reduce by any deposits for those given criteria.

I've tried numerous things from DSUM, to SUMIF with IF, to SUMPRODUCT.

View 9 Replies
View Related
Apr 28, 2009

I received an answer to my original question and now have a new question but I wanted to reference my original for the history. I posted my new question at the end of my original thread.

[url]

View 9 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
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 14, 2009

I have no problems figuring out SUMIF or SUMPRODUCT with multiple criteria, but I'm trying to help someone with an issue that the second criteria is actually not all-inclusive.

Column B - Lots of numbers, the SUM range

Column C - Yes and No flags. Y or N

C1 = maximum number of matching items to include.

So, =SUMPRODUCT((C2:C100="Y")*(B2:B100)) sums up ALL the Yes rows, but the guy wants to restrict it to the last X matches, and put that X factor in C1. So if C1=5, only the last 5 Y matches are included.

View 9 Replies
View Related
May 5, 2006

i'm trying to caclulate the max number based on two colomns criteria. For example if column A equal MC1 and column B equal 1 then find the max value of that group. I tried using sumproduct but so far it only works if i have one criteria wasn't too sure how to use it for two criterias. See attached file

View 3 Replies
View Related
Sep 16, 2009

I haven't been this deep into excel before. The deeper I look, the more potential I recognize, the more amazed I get. That being said, I have come to a tough count issue. Let me attempt to explain as precisely as possible.

My current worksheet is large but I am only particularly concerned with two columns of information (Regions) and (Days). The logic I am attempting is something along the lines of Count If Region = East, or West, and Days is greater than 0, less than 60.

I am open to any and all suggestions on how to tackle this situation. I have been able to achieve similar counts by using pivot tables but the dynamic nature of these two columns presents some difficulties that my “new user” mind has been unable to work through.

View 9 Replies
View Related
Oct 1, 2009

I have 2 columns of data being populated by vlookups

Column H is both numbers and text. Column I is Text and blanks. I need to be able to find only numeric values in column H greater than 0 and compare those occurrences with the corresponding cells in column I and if column I has a text entry (not a blank space) than to count that and at the end give me a total number of times these 2 criteria are met. As an example.

If column H has a text entry then don't count it.

If column H has a number less than zero then don't count it.

If column H has a number greater than zero but column I is blank then don't count it.

If column H has a number greater than 0 and column I has a text entry then count it.

I've tried using many variations of a sumproduct formula and none of them work.

This formula counts all instances where column I has a text entry without checking column H for a number greater than 0.

=SUMPRODUCT(--(H2:H110>0),--(I2:I110<>" "))

Or it's possible that the formula is counting the text entries in column H as a number greater than 0 but I'v tried excluding text using this..

=SUMPRODUCT(--(H2:H110>0&<>"*"),--(I2:I110<>" "))

but this causes an error in the formula somehow that I can't figure out. I even tried this

=SUMPRODUCT(--(H2:H110>0&"*"),--(I2:I110<>" "))

and I get a formula that counts only the times text appears in column H and column I together which is not what I want either.

I'm self-taught on Excel so I know there's a lot I'm not understanding about creating formulas like this but I need to have this working by Friday and I just want it to work.

View 8 Replies
View Related
Jan 26, 2010

Attached is my sample workbook. There would normally be 600+ employees with multiple rows per employee. I would like Cell O3 in the Premium Calculation Worksheet to look at the Premium Contribution Report, and if Row A contains the employee number (A3) AND row C contains "H&D" I would like it to sum row E.

I included the sumproduct formula I tried to put together but I'm getting an error, so I'm not sure what I've done wrong. The reason I have it referencing "O2" instead of just inputting "H&D" is that O2 could be any number of plans - I have multiple rows with different plans and I need it to pull in all the data.

View 2 Replies
View Related
Sep 24, 2012

I have two sets of criteria I want to incorporate into one formula. In the first column, if the criteria is matched, it will check the criteria in the next column. The criteria in the second column is something that resembles the 'or' function. So if criteria equals x,y, or z, sum the results from the data range c3:c98. I tried writing it like this.

=sumproduct(--(a3:a98=a),--(b3:b98=x(or(b3:b98=y,b3:b98=z))),c3:c98)

View 1 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
Jan 28, 2010

I want to count the number of unique, or distinct, company names in column [C7], subject to two conditions that will exclude certain unique company names from the count.

I should also point out that most company names appear multiple times in column [C7].

a) Count the unique company names in column [C7]...

b) ...including only those companies who have at least one "Yes" in column [C8] somewhere among their records

c) ...and who also have at least one value equal or greater than "1" in column [C15] somewhere among their records

Note that that there is no requirement that the "Yes" and the "1" ever appear in the same record.

View 2 Replies
View Related
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
Jan 10, 2014

I've not used SUMPRODUCT previously and can't understand how to get results for the attached.

I've tried SUMIFS but it doesn't work because I'm looking down columns and across rows, I'm assuming.

I've attached a summary of what I'm trying to achieve. I want to sum all costs with an R,P,I,G, etc. in column C for December '13 (E3) in the top table.

The second table is actually in a different sheet but is the source of the data I need added.

Sumproduct P&L.xlsxâ€Ž

View 11 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
Nov 8, 2007

I am trying to sum data based on multiple criteria..

The english version of the formula is Sum all refunds for Store during week

Original Data Format: ....

View 14 Replies
View Related