SUMIF/COUNTIF With 2 Criteria
I am trying to extract some data from a large spreadsheet and having problems...
Column C contains text descriptions, e.g 'Description One', 'Description Two', 'Description Three'
Column O contains a date.
I need to count the number of items that have a date prior to 1st April 2007 and have certain text contained in the full text string value in column C e.g. 'One'
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Formula- To Pull Cell Values Similar To A SUMIF Function (SUMIF(range,criteria,sum_range))
I am trying to pull cell values similar to a SUMIF function (SUMIF(range,criteria,sum_range)). For example, in A1 I use a data list created from data elsewhere on the spreadsheet. In the data I created elsewhere, there are 2 columns being used. The 1st column is the information that is being used to create the list and the second column contains specific values (number or text). In the dropdown menu I select an available value (text or number) . When I have selected that value I would like cell A2 to show what the cell directly to the right of it shows from the data I have elsewhere in the spreadsheet as mentioned. I have tried the SUMIF function however it seems to exclude certain values (number or text) and I am not sure what else to use.
View Replies!
View Related
SUMIF And COUNTIF
I want to calculate the average perofrmance % of 8 lines, the data isn't in one set of rows and some lines may not have values so I'm trying to account for this in my summary. The code I'm struggling with is this... =SUMIF(C5,C10,C15,C20,C25,C30,C35,C40,">0")/COUNTIF(C5,C10,C15,C20,C25,C30,C35,C40,">0")
View Replies!
View Related
COUNTIF And SUMIF Formula
It's been awhile since I've used excel formulas so I'm very rusty but I need to count the number of times I see a particular item in a list as long as it has another criteria as well. Example List and Result are below: I want to count the number of AAA Rewards that Alex has.... List #DateEmployeeAmountReward Type 14/17/07Alex$5.00AAA Reward 24/19/07Joe $10.00AAA Reward 34/19/07Alex $5.00 Store Certificate 44/20/07Alex $10.00 AAA Reward Result Employee # AAA Reward Amount # Store Certificates Amount Alex Brian Joe
View Replies!
View Related
SUMIF/COUNTIF = Average
I have a report that has thirteen tabs. Tab one is Jan; two, Feb; three, Mar, etc. The last tab is where I'm consolidating all the data into a report. In Row 2 of Jan-Dec are the days of the week. In Row 30 I have the data. I am figuring the average of each day of the week for each month as well as a total average year-to-date. Here is my formula for Jan and Feb: =SUMIF(JAN!$2:$2,"MON",JAN!$30:$30)/COUNTIF(JAN!$2:$2,"MON") =SUMIF(FEB!$2:$2,"MON",FEB!$30:$30)/COUNTIF(FEB!$2:$2,"MON") Works great after the month is over - but now I'm in March and the formula is adding all data form row 30 that falls under MON and dividing my all MONs even if it does not have data. How do I nest in the COUNTIF statement to only count MON IF there is data in row 30.
View Replies!
View Related
Count, Countif, Sumif
I've got a table that contains a lot of text that i need to summarse in another table. Its really just two columns the first contains a group name and the second a result. What I need to work out is the number of times the result occurs for each group. But every work sheet function I've entered fails.
View Replies!
View Related
Ignore Blanks Using =SUMIF/COUNTIF
I've come across a problem using the =SUMIF/COUNTIF function in excel. It incorrectly calculates blank cells as zeros. =SUMIF(D4:D54,"Solar",T4:T54)/COUNTIF(D4:D54,"Solar") The correct output should be 100%, but shows as 66.67% because of the blank cells in the column. How do I correct this?
View Replies!
View Related
Countif, Sumif Or Sum Product
I have a table with 3 columns, Project Person Hours A B 1 A C 2 A B 3 So on project A persion B has worked 4 hours. I seem to be able to use sumproduct for counting how many times the person is in the project but I cannot seem to add on the final hours part.
View Replies!
View Related
Weighted Averages Using SUMIF And COUNTIF
DATA: D1:D11 contains either blank cells or a dollar figure from a payroll check. E1:E11 contains either blank cells or the date of the check. Current Situation: Using the following formula I obtain a weighted average of the pay checks, not including the largest. =(SUM(D1:D11)-MAX(D1:D11))/(COUNT(D1:D11)-1) Problem: I desire to calculate the weighted average based on the checks issued only prior to a certain date. I have used SUMIF() in other parts of the workbook successfully but needing to withold the largest value has presented me with a problem I can not find a solution to. =SUMIF((E1:E11,">="&TargetDate,D1:D11)-MAXIF(E1:E11,">="&TargetDate,D1:D11))/COUNTIF(E1:E11,">="&TargetDate,D1:D11)
View Replies!
View Related
COUNTIF Or SUMIF: Changing Amount
I have a data range with 3 columns - A -employee number, B -charging number, and C -charge amount. "A" shows multiple times and must be associated with value from "B". I am looking for totals of "C" for any combination of "A" and "B". Ex. A B C 1 101 6 1 201 7 1 101 4 2 301 6 2 101 1 2 201 4 3 201 5 3 401 7 etc. I am looking for totals (from column C) for A and totals for A with defined B. e. g.: 1 for all 101 1 for all 201 1 total 2 for 201 etc..
View Replies!
View Related
How Do I Set Up A Countif Or Sumif That Is Multiconditional
I often try to set up a countif or a sum if that is conditional on two or more conditions. For instance - I have Radio stations listed in one column, flight dates in another column, number of contacts for each flight in another column and appointments created from those contacts and finally, revenue generated form those appointments. One example would be to sumif column one equals the radio station and column two equaled the flight date then give me the revenue.
View Replies!
View Related
Combine SUMPRODUCT, SUMIF, COUNTIF Within Date Range
The attached spreadsheet has a "master" workesheet in which I enter customer info, salesperson info, and date. The totals spreadsheet automatically calculates number of sales, contact value. I need to modify the following formulas to only calculate the data within a date range shown in 2 cells. =COUNTIF(Master!A1:A176,PayPeriod!A4) =SUMPRODUCT(--(Master!A1:A176=Totals!A4),(Master!K1:K176)) =E4SUMPRODUCT(--(Master!A1:A176=Totals!A4),(Master!K1:K176))
View Replies!
View Related
SUMIF/COUNTIF- Several Numbers That Are Contained In Non-adjacent Cells
I have several numbers that are contained in non-adjacent cells. I need to create a cell that will evaluate the cells individually to make sure that they are within a certain range. Once that is done, I want the remaining numbers that are within the range to be averaged. How the heck can I accomplish this task? SUMIF and COUNTIF will not allow me to use non-adjacent cell ranges.
View Replies!
View Related
Two COUNTIF Criteria
What I need to do is count the number of “Cs” in a column based on a date in another column but in the same row. I have tried something similar to this: COUNTIF($C$1:$C$20,"=today()")+COUNTIF($E$1:$E$20,"=complete") but is does not work. If the date in the column is less than or equal to a date specified in a cell in another worksheet, I want it to count the C in the row (if there is one).
View Replies!
View Related
Countif Using 2 Criteria
I have a spreadsheet with a list of months numbers and average turnarounds. Each row represents a different factor, so there are multiple rows for each month. eg Month Turnaround Time 10 5.2 10 6.7 11 1.1 9 8.3 11 5.4 10 6.1 etc What I am after is something that will count the number of instances where the turnaround time is above a certain limit (eg 6.0) for each month.
View Replies!
View Related
COUNTIF With Two Criteria
I use Excel 2003 and I have the following problem: I have 3 columns, A containing a list of employees (MICHAEL, BOB, MIKE, etc.) B containing their work starting hour (8.00, 8.30, 9.00, etc.) C containing the possible employee absence reason (ILLNESS, HOLIDAY, INJURY, etc.) I would like to write a formula that counts the number of employees who have a work starting hour within 7.00 and are not absent. A possible table is this one: NAME START ABSENCE MICHAEL 6.30 BOB 8.30 MIKE 9.00 HOLIDAY BRIAN 7.00 TOM 6.30 ILLNESS The formula I'm looking for should calculate "2" (because MICHAEL and BRIAN are the only 2 employees starting work hour within 7.00 and not absent). As I have thought it could be useful, in another worksheet I have inserted: in A column the list of all the starting work hours: 0.00 (A2), 0.30 (A3), 1.00 (A4), 1.30 (A5), ... 7.00 (A16), 7.30 (A17), ... 23.30 (A49). in B column the list of all the absence reasons: ILLNESS (B2), HOLIDAY (B3), INJURY (B4). I have defined 2 names, the first called EARLY_MORNING (that I have associated to the range from 0.00 to 7.00 of work starting hours column, that is A2:A16), the second called ABSENCE_REASON (that I have associated to the range (B2:B4) of absence reasons' column). What kind of formula can I write to obtain what I want (using the 2 names EARLY_MORNING and ABSENCE_REASONS defined in the other worksheet)?
View Replies!
View Related
Sumif With Two Criteria
I don't know why I can't figure this out, but it has been too long, but I need a formula that looks for yesterday's date and adds up any time that matches that date and that person from the list. So like in this example, for yesterday, my answer would be 1 hour for Ashley. A B C 1 1/7/09 Ashley 0:10 2 1/8/09 Ashley 0:10 3 1/9/09 Bob 0:10 4 1/7/09 Ashley 0:50
View Replies!
View Related
SUMIF With Criteria
I am trying to take a census by date and name for every half-hour using the attached data. So for example, I would like to know how many people Roger had that walked-in and walked-out for each date on the spreadsheet and during what hours. The census would be every half-hour and not double count times. I could actually deal with just the min and max time for the day (by date and name) and having some indicator (say a one or an X) in the cells when Roger had at least someone anytime between walk-in and walk-out. It's kind of like getting a "time worked" based on the data that's provided to me. So I know how many hours Roger worked by day.
View Replies!
View Related
SUMIF 2 Or More Criteria
I have a workbook with several different worksheets. I am attempting to pull data from a worksheet, I will name it sheet1. Sheet1 has 6 columns. The data I am interested in is in B and F. Column B has account numbers ranging from 1 - 1,000,000 and column F has the total account balances. What I am attempting to do is to sum the data in F that falls between a criteria in B. For example I want the sum of F that correspond to accounts 4999 thru 7555. I have tried several variations of the same formula from my online search and am still unable to get it to work properly. I have tried using ">=4999" "<=7555" and I cannot get it to work properly. It seems fairly straight forward, but I am perplexed as to what I am doing wrong.
View Replies!
View Related
Countif With Two Ranges And Two Criteria
I am using a work sheet where I want to count if Column A has one criteria and column B has another example column B states is used to track contracts it can be vendor column L tracks status it can be open, received, or closed I want to count the cell if the vendor name matches and the status is open also if that is possible is the same possible with 3 ranges and criterias?
View Replies!
View Related
COUNTIF Based On Two Criteria
I have data in two columns. Column B has calculated string values of "BMAJOR" and "BMINOR" and column C has date values. I am trying to get a count of how many occurences of "BMAJOR" or "BMINOR" occur for every week starting today. I have column AJ which returns today() and column AK which returns today()+7. I can individually count number of occurences of dates that fall within these dates with the following formula. This for row 2. =COUNTIF('Decision Tree'!$C$4:$C$215,"
View Replies!
View Related
Countif Formula Criteria
I got a CountIF problem. I need to find the formula to work out the number of items that have no Delivery Key assigned. I tried, =COUNTIF(Data!O1:O4000,"0") but does not work as it just comes up blank. I also tried =COUNTIF(Data!O1:O4000," ") but the same result. I.e =COUNTIF(O1:O28," ") on the sample spreedsheet. I attached a sample copy of the Spreedsheet. I tried to give it a shot but to no avail.
View Replies!
View Related
COUNTIF With Mutiple Criteria
In Row 3, starting at cell C3, I have a list of Stores in the format Store A (Town 1), Store A (Town 2), Store B (Town 1) etc In Rows C4 to CA7 I have 1 or -1 I want to count the number of instances of 1 for Store A I have tried the following formula, which is returning 0 {=SUM((C3:CA3="Store A *")*(C4:CA7=1))}
View Replies!
View Related
Multiple CountIf Criteria
In Excel 2003, I need a countif to check for 2 criteria: (1) the left function looking for the value "Territory" in column A and (2) value > 0 in column G. I only want to count the rows where both the criteria are met. I have tried different combinations of countif including "and" in the formula, but I cannot get it to work. What is the proper syntax?
View Replies!
View Related
Countif Multiple Criteria.
I'm trying to count multiple criteria from a second page in a work book, all the formulas i've looked up and tried do not seem to work... here's the formulas i've tried. DKOBULAR is the name of the 2nd page. D is the column used for the different resolves. =COUNTIF(DKOBULAR!D:D="resolveA")+COUNTIF(DKOBULAR!D:D="resolveB")+COUNTIF(DKOBULAR!D:D="resolveC")+ COUNTIF(DKOBULAR!D:D="resolveD") =COUNT(IF(DKOBULAR!D:D="resolveA",IF(DKOBULAR!D:D="resolveB",IF(DKOBULAR!D:D="resolveC",IF(DKOBULAR! D:D="resolveD")))))
View Replies!
View Related
Using Countif With Multiple Corresponding Criteria
what I'm trying to do is to make a logbook for a machining center. Each part has an op10 and an op20, essentially front and back. And each part number falls into the category of OS or FS. I've used AND logic to make tables in hidden columns to be used by a countif statement to determine my totals. I.e. to determine if a scroll is completed, op20 has a a value of 1 AND column C is "OS". I use =IF(AND(A9=1;C9="OS");1;" ") Then I countif criteria is 1 in the column i created with that statement. That works just fine. Now what I want to do is to be able to create daily totals of OS and FS by simply modifying a variable date in a formula. So I'd like to essentially say: Countif Column C =OS and Corresponding column D = 1, and corresponding Shift date = 10.02.12(date to be variable). I'm at a wall here. Is there any way to do this somewhat simply?
View Replies!
View Related
COUNTIF Criteria In VBA
What vba function can I use that checks criteria much like SUMIF or COUNTIF uses? In other words, say you want to write COUNTIF that only includes visible cells... Public Function CountVIf(rng As range, criteria As String) Dim cell As range, cmd As String For Each cell In rng If cell.RowHeight <> 0 And cell.ColumnWidth <> 0 Then cmd = "COUNTIF(" & cell.Address & ",""" & criteria & """)" CountVIf = CountVIf + Evaluate(cmd) End If Next cell End Function How can I do this without having to rely on Evaluate("COUNTIF...."?
View Replies!
View Related
Countif Multiple Criteria
I need to create a formula which counts the number of times a username appears in column X based on a given value in Column Y. This data will not be static - will need to be refreshed regularly. Countif does not support multiple criteria - what is the best way to create this formula?
View Replies!
View Related
COUNTIF With Cell As Criteria
I have an Excel file with 42000 records that are identified by a Reference in Column E. There should be two records per reference in the file. I need to identify all records that only appear once, as well as all records that have more than 2 entries.
View Replies!
View Related
Sumif Between Date Criteria
I just became a member of your forum, so please forgive me if I am not concise enough or take too long. I imput the number of hours associated with week ending dates. A C D Date Hours OT 01/08/2006 6 2 03/12/2006 8 04/16/2006 3 1 I need to add up the hours for each quarter for Hours & OT Jan-Mar Apr-June etc This is what I came up with, but it doesn't give me the correct value = SUMIF(A3:A75,"<="&DATE(2006,03,31),C:C)
View Replies!
View Related
SUMIF With Sequential Criteria
In the formula (range, criteria, sum_range), I have a fixed range and a fixed summary range for each column, i.e.: ($F$3:$F$805, "criteria", O$3:O$805). HoweverI am trying to sum up units by income level (columns D, E, and F) using information from elsewhere on the sheet. I am doing this for each city, which entails changing the criteria for all of the cities I am using three times (once per column). Is there a simple way to autofill the criteria? They are just names of cities, all in the same column, COLUMN B. Or do I have to type each individual change?
View Replies!
View Related
SUMIF 2nd Criteria
Im using a formula to identify a job description then add up the quantity which works fine. =SUMIF(Master!A1:A10,"Bread",Master!C1:C10) But my problem is, I have a second description in column 'B' for example called 'White'. I've been trying to get the formula to look at column A & B and if they match criteria in formula, then add up C. But can't seem to get it to work with the second criteria.
View Replies!
View Related
Nesting A New Criteria In Sumif
I have the following Formula: =SUMIF('Sheet2'!C:C;Sheet1!B3;'Sheet2'!E:E) In this way I got the sum of all items located in column E wich have the same value than in B3 in Sheet 1.... How could I change the formula in the way that only returns the Values in Sheet2 E:E when (If equals to B2 and C2 in Sheet 1 then Sumif) The value which I want to use to filter is in the same columns in both sheets
View Replies!
View Related
Non Working SUMIF (more Than 1 Criteria)
Normally, sumif formula works well with problems that have one criteria to take into consideration. With the table that I have, I'm unable to accurately use SUMIF. I'm not even sure if I should use it since what I really need is Average. SUMProduct proves to be even harder to use for me. Here's what I have: ID CODEStatusSTARTDUEENDTURN AROUND TIME (TAT) 122307Closed1/142/81/3112 DAYS 122265Closed1/142/22/214 DAYS 122265Closed1/142/101/3112 DAYS 122307ATD1/131/21---------- 122253Closed1/121/221/229 DAYS 122265ATD1/221/30---------- Other details: I used =SUMPRODUCT(--($B$21:$B$31=C4),--($D$21:$D$31=$D$3)) to count the total number of a specific task status per ID code, say, closed and ATD. I manually added TAT that has "Closed" status on a per ID code basis. Ex: ID Code# of Closed StatusAverage TAT 12265213 DAYS (14+12/2) Is there a formula that will give me the average TAT taking ID Code and "Closed" status as conditions/criteria? What about Vlookup, average?
View Replies!
View Related
SUMIF Based On Two Criteria
I want a formula to sum all values if two different criteria match. I've attached a sample below. The TaskID and the Time Entry ID are the two data points I'm searching for and the totals column is where my sum statement would be. The numbers are being pulled from another worksheet.
View Replies!
View Related
Multiple Criteria Countif Or Sumproduct
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 Replies!
View Related
Multiple Criteria For Countif Functions
I need to create a formula that counts the number of times that an age range appears within a column. In column G, there is a list of ages based on a demographic collection. The ages range from 13 to 50+. I want to designate another cell to count the number of times the characters between 13 and 18 occur within that column. I have =COUNTIF(G8:G20,"13") How do I add "14", "15", "16", "17", and "18"?
View Replies!
View Related
COUNTIF Function With Multiple Criteria
I need to count rows that meet 2 criteria. I have seen this help page http://www.ozgrid.com/Excel/count-if.htm but that counts rows with "criteria 1" OR "criteria 2"... I need to count rows that fulfill "criteria 1" AND "criteria 2" ie - count the rows that have todays date AND a cell that says "COMPLETE" ideally it would be as easy as "=countif(A:F,"today()","COMPLETE") but that doesn't work... any way around this???
View Replies!
View Related
Countif Criteria With Named Reference
I'm trying to use a countif formula with a named reference but the formula is not calculated correctly. My example would be: countif(A1:A5,<max) with max being the named reference. I also tried various options using quotes around the named reference (ex. "<max") but that doesn't work either
View Replies!
View Related
|
TRACKER
