SUMPRODUCT  Count Multiple Conditions
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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Sumproduct To Count From 2 Conditions...
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 Replies!
View Related
Sumproduct With Multiple Conditions
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 Replies!
View Related
Count Unique Logs With Multiple Conditions Of Multiple Sheets
I've got no clue about all this, but I've had to get specific formula examples and fill in the blanks in order for my timesheet to work. There's just one final problem if somebody could please help. This is a timesheet for a 5 day work week. I need to count the number of unique log numbers for a specific activity. The log numbers counted must be unique across the entire week, not just for each day, which means I want the formula to count the unique log numbers across multiple sheets. The formula also has multiple conditions. I got 2 columns. The first part of the formula needs to verify a word, say, "split" and if it does it checks the adjacent cell for a unique log number. If both arguments are true, it counts the log as 1 unit. Here is a working formula for only one page. =COUNT(IF(D4:D29="split",IF(FREQUENCY(C4:C28,C4:C28)>0,1,))) Here's 2 problems with this formula: 1. I will count if it encounters a blank cell in the Log numbers the first time (which will happen as not every activity we do has a log#), but it will stop counting if it encounters a second blank cell. 2. I don't know how to make it work across several sheets. This is an alternate formula which works and skips the blank cells, but I don't know how to add the multiple condition of "split" and to have it work across multiple sheets. I just copied it Microsoft. As I said, I don't understand it, I just fill in the blanks. SUM(IF(FREQUENCY(IF(LEN(C4:C29)>0,MATCH(C4:C29,C4:C29,0),""), IF(LEN(C4:C29)>0,MATCH(C4:C29,C4:C29,0),""))>0,1))
View Replies!
View Related
How To Use Count With Multiple Conditions
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 Replies!
View Related
Count Cells Using Multiple Conditions
I have 3 sets of data  Process, Step, and Time Range. I am trying to generate schedules based on Process, with Step being the vertical axis, and Time Range being the horizontal axis. Hence, I'll have schedules showing that for each Process, the number of cases that each Step that has taken, for example, "07 Days", "814 Days", etc. I have four Processes in total  A,B,C, and D; 15 Steps from 1 to 15; and 7 Time Ranges. I have attached a sample .xls showing the schedules that I would like to popuple the counting onto. A little more details, not all Processhas all 15 Steps, i.e. Process A has Step 1 thru Step 9 only, Process D has Step 1 thru Step 15 excluding Step 11 & 12I am actually creating a template where data will keep on expanding and updatingwould prefer excel formula rather than VBA code as I am not very familiar with what to do with VBA codes
View Replies!
View Related
Count Rows Matching Multiple Conditions
I want to count all instances if the following conditions are true. In quotations, are the names that I am using for column ranges. Here are my conditions, I want to count the rows that have the following conditions. When "dates" or J2:J25 is less than or equal to today's date AND "HTeam" or W2:W25 is equal to Civil AND "Percent" or K2:K25 is equal to 100
View Replies!
View Related
SUMPRODUCT With Multiple Criteria: Count The Number Of Documents
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 Replies!
View Related
Advanced Filters  Having Multiple Filters And Conditions (Unique Count)
I have a problem with the attached spreadsheet. I have certain letters (A,B,C etc.) that are shipped to various regions. I would like to have a count on top to count the total number of orders, but one that also counts the total number of unique orders. However, this unique count has to be dynamic and must be able to adjust accordingly to the filters (by default, if no other filters are applied, should be 15). For example, if I apply the "Ship To" filter to Canada, the total number should be 19, but the unique count should be 12. If I change the "Ship To" filter to US, the total number should be 9, and the unique count should be 7. I've tried to use the advanced filters but if I apply the unique entries filter, it is only a one time calculation. Also, the advanced filter gets rid of my other filters.
View Replies!
View Related
3 Conditions  SUMIF Or SUMPRODUCT Or...?
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 Replies!
View Related
Sumproduct Where Two Conditions In The Same Column
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 Replies!
View Related
SUMPRODUCTformula With Changing Conditions
I have a few varieties of SumProductformulae 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 Replies!
View Related
Count By 2 Conditions
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 Replies!
View Related
Count The Months Within The SUMPRODUCT
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 Replies!
View Related
Count Conditions On Tabs
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 Replies!
View Related
Count For Instances With 2 Conditions
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 Replies!
View Related
Sumproduct  Count More Than One Criteria By Date
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 Replies!
View Related
Sum And Count Using SumProduct Function
*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 Replies!
View Related
Count The Values Through Sumproduct In VBA
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 Replies!
View Related
Count Cell Value If Conditions Met
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 Replies!
View Related
SUMPRODUCT  Counting Records  How Do I Not Count Dupes
I'm using the following formula to get a count of records that have a matching function name that is in B10. =SUMPRODUCT(('Oct2007_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 Replies!
View Related
Countif Or Sumproduct Function (count In A Range)
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 Replies!
View Related
SUMPRODUCT Formula (count How Many Of A Particular Type Of Entry There Are)
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 Replies!
View Related
How To Satisfy Multiple Conditions Using Multiple Criteria
I'm trying to divide my data into 6 different groups, based on 2 different criteria. First, I am not sure how to write the logical test to take 2 columns of data into consideration (using "&" and "AND" do not work; I am not sure what else to try), and second I can't figure out how to write the formula so that it can select from 1 of 6 conditions. So, overall here is what I want: If DL2=3 and CK2=1, then I want this to be labeled as '1' If DL2=3 and CK2=2, 2 If DL2=1 and CK2=2, 3 If DL2=2 and CK2=2, 4 If DL2=1 and CK2=1, 5 If DL2=2 and CK2=1, 6 These 6 conditions cover all possible combinations of numbers in the two columns.
View Replies!
View Related
Remove Multiple Data By Multiple Conditions?
1. I have 4 columns (let's say D,E,I,J); D E I J Account1 Sum1 Account2 Sum2 151 93,79 BF2479 30,00 1476757 93,79 BF7 22,00 BF2479 30,00 151 93,79 86041 84,03 23235 30,00 534 6608,45 2. I need to clean the multiple identical data in the list by both Account no. and Sum (of course the list is much longer!) so that the list should look like this: D E I J Account1 Sum1 Account2 Sum2 1476757 93,79 BF7 22,00 86041 84,03 23235 30,00 534 6608,45 I must say that I am a beginner in Excel, so I tried to use this: =IF(OR(E5=$J$5:$J$430);IF(INDEX($I$5:$I$430;MATCH(E5;$J$5:$J$430;FALSE);1)=D5;"";E5);E5) for column E, and =IF(OR(J5=$E$5:$E$452);IF(INDEX($D$5:$D$452;MATCH(J5;$E$5:$E$452;FALSE);1)=I5;"";J5);J5) (which is reverse of the first one) for column J; (The ranges are the ones used in the real sheet), not for the ones in the example above!); The formula does seem to eliminate a dew unwanted data, but does not give me all the wanted results!
View Replies!
View Related
Sum On Multiple Conditions In Multiple Columns
My data looks somewhat like this : X Y Z DOU Quarter Sum 0 Q1 = sum of 1's in Q1 1 Q1 = sum of 1's in Q2 1 Q2 = sum of 1's in Q3 0 Q2 = sum of 1's in Q4 1 Q3 1 Q1 1 Q4 0 Q2 A cell in the SUM column must contain the sum of 1's in column DOU if under column Quarter the value is Q1. Similarly for Q2, Q3 & Q4 if only the corresponding value under DOU is 1. Its like.. count the number of 1's per quarter and come up with the sum in 4 distinct cells for each quarter.. i.e. quarterwise sum/count of 1s
View Replies!
View Related
Match And Count Unequal Ranges With Conditions
I have a problem finding the correct formula for counting matches with conditions between 2 nonequal ranges in Excel. The sheet is a try at making a working schedule template a bit automated. For Week 1 each cell in the H16:H25 has a dropdown list (originating from BD30:BD50) where a work position can be chosen. The fixed list in BD30:BD50 starts with “<<SELECT>>” which is the default choice for the cells in H16:H25, and then “HOLD” before continuing with various work position names. K16:K25 is shift number 1 on Monday, L16:25 is shift 2 on Monday, and so on until Shift number 6. Then the rest of the days of the week follow (each with 6 shifts). Monday through Sunday (with 6 shifts for each) ranges over K16:AZ25. In the cells in K16:AZ25 the following can be entered: “x” (work), “o”(off), ““ (leave). The issue is the formula in each of the K26:AZ26 cells which are to total each of the shift columns . I want to count all the “x” in each column, but ONLY if the positions chosen in H16:H25 matches one of the positions in the list in BD30:BD50. NOT if a cell in H16:H25 displays “<<SELECT>>” or “HOLD” (even if it has a “x” entered in one of the Shift cells). For example: .....
View Replies!
View Related
Count And Sum Cells Meeting Two Conditions
I'm working out a schedule for work. Row 1 contains 31 days(columns), Row 2 28 days, Row 3 31 days...and so on for the 12 months of the year. I've formatted each Friday, Saturday, Sunday and Holiday with color. Fridays are blue, Saturdays are green, Sundays are yellow, and Holidays are red. MondayThursday are no color. Next, I fill in each day with an employee name. Now the hard part...I want to count the number of times an employee name falls on a MondayThursday, Friday, Saturday, Sunday and Holiday. At the bottom of the worksheet I'd like to see something like this: Jones: Friday 4 (total number of days jones is in a blue box) Saturday 5 (...on a green box...and so on...) Sunday 3 Holiday 2 MondayThursday 50 For each employee name. Sounds easy, right? I can't get it to work!
View Replies!
View Related
Sumproduct Query: Count Number Of Days Holiday
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 BG on a row and want to count number of days holiday.
View Replies!
View Related
Count Unique Entries In One Column That Meet Conditions
I tried to ask this question yesterday  but it was a followup question stuck at the bottom of a thread. So, with your indulgence, here is a simpler version of the question, complete with an attached spreadsheet, if you wish to use it. I also closed the other thread by marking it "Solved", since it answered my initial question.] The situation: I have two columns of data. The data is not in alphabetical order, and every column includes duplicate values. namegender jones m martinf smithf collinsf wilsonm jones m martinf hughesm wilsonm martinm smithf west f jones m west f martinm The challenge: In one cell, count the number of unique names that appear in the name column 3 or more times... with the additional condition that each unique name (which appears at least 3 times) must include at least one one woman! The correct result: ...
View Replies!
View Related
Count With Conditions & Doesn't Exist In List
I have a Sumproduct formula to count instances of a particular event (from a list of events) based on multiple criteria. I am trying to utilize the same method to count instances of all events not defined in the list of events but I would welcome any solution In the attachment, Defined list of events A4;A5 (this is just an example, the actual list is approx 100 events) Data being counted F2:N10 (actual data approx 1000 rows) My working formula is in cells B4 through D5 My not working attempt to adapt the formula B6
View Replies!
View Related
Sumproduct (count The Number Of Unique, Or Distinct, Company Names In Column)
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 Replies!
View Related
Dividing Output Of Sumproduct Formula By Count Of Specific Cell Contents
I've got the following sumproduct formula (which seems to work): =SUMPRODUCT((ISNUMBER(SEARCH($B17,Product_Keywords))),(YEAR(Invoice_Date)=H$3),(ROUNDUP(MONTH(Invoice_Date)/3,0)=H$4),(Invoiced_Amount)) B17 is a keyword to be found in the Product_Keywords range I'd like to modify it so that: ifthe cell in the range Product_Keywords also includes "," thendivide the sumproduct by the number of commas+1 Presumably I could add (ISNUMBER(SEARCH(",",Product_Keywords) as one of the conditions, but how would I keep track of the number of commas in the cell?
View Replies!
View Related
Make One Cell Count Twice In AVG If Certain Text Conditions Apply 2008
I am using Excel to tabulate votes for a contest. Judges have given a number to each entry, and but certain judges' opinions need to count twice as much as other judges' opinions based on their qualifications. I've attached the file to help illustrate what I'm trying to do. Morris's votes need to count twice for all Photography or Web Design entries, and Clark's votes need to count twice for all Graphic Design or Web Design entries. I know I can do this manually by simply copying the number into a blank cell in another column (like the blank column between Morris and Clark's names), but is there any way to make Excel do this for me?
View Replies!
View Related
Count Number Of Cells That Meet Specific Conditions  Error Messages
I have a spreadsheet which is linked to several other worksheets. I have managed to include formulas to count how many cells have numbers between 101 and 5000 by using this formula  =sum((h2:h500>=101)*(h2:h500<=5000)) but now I want to count the number of cells in another worksheet that are equal to or less than zero. When I use the same formula as above it counts all the blank cells. I have tried using a countblank formula and then deducting this from the result, but unless the other worksheet is open the countblank formula does not work.
View Replies!
View Related
SUMPRODUCT Multiple Variables
I trying to figure out how to calculate a field based off multiple variables that are dependent on another cell range. I'm looking to count everything in the C8:C49 cell range that contains either "BETA" or "FINAL" in the cell but ONLY if the F8:F49 cell range contains "In Test")
View Replies!
View Related
How To Use SUMPRODUCT With Multiple Criteria
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 Replies!
View Related
Multiple Lookup Or Sumproduct
My data range is E2:H15. A separte calculation would generate a number in C2. C4 uses Index(match) to finds the closest Vol match greater than or equalin column E. Column E is sorted Desending for the Match. The user then inputs two maximum values for W (column F) and H (col G). The example problem should return 20 for L. Help! I have been trying every combination of index, match, lookup etc. Sheet1 *ABCDEFGH1****VolWHL2*Minimum Vol463*800104203Lookup 1Closet Match Greater Than or Equal To480*64084204Lookup 2Input Max W =8*600103205Lookup 3Input Max H =3*600104156****48083207*Solve for L20*48084158****48064209****4501031510****4001041011****360831512****360632013****360641514****320841015****30010310Spreadsheet FormulasCellFormulaC3=INDEX(E2:E15,MATCH(C2,E2:E15,1),1) Excel tables to the web >> Excel Jeanie HTML 4
View Replies!
View Related
