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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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 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 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
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
Nested SUMIF Statement Or Multiple SUMIF
I need to perform 2 SUMIF's on 2 columns of data to return a result and I'm not quite sure the best way of doing this. I'll give an example below. I have 2 columns of data, both numeric and the SUMIF needs to say if H1:H100="10" and also if J1:J100="907". I can perform one or the other but not both.
View Replies!
View Related
SUMIF In A Column: The First Instance Of Each SUMIF
I have many kitchens using the same recipes. I need to distill information down until I've got a summary of how much is being made. Uploaded is a condensed version of the point in the process I'm having difficulty with. This workbook will pull information from 8 other workbooks and give me excatly what everyone made on any weekday. And from there, with the kind help of this forum, I figured out how to do a SUMIF based on the recipe number. And it summed up all instances of 'Recipe X' being used. However, it continues to SUMIF itself all the way down the page... which is good, because of how recipes are chosen for each kitchen. However, I only need to report one instance of each recipe. In the uploaded example (and I apologize for the colorful sheet, but it helped me double check what I was working on.) ... I only need to report the PURPLE results elsewhere... the first instance of each SUMIF.
View Replies!
View Related
How To Countif
I have following data (two columns Parent and Child), now I want to apply Countif on Child cell. But in Countif I want to provide the criteria...let say only count those childs whoes parent is A. How to do this in Excel. Parent Child A e A f B g B h B i C j C k
View Replies!
View Related
Countif: Others
I'm reasonably new to Excel, and have a fairly basic question to check out: I have been using the COUNTIF function to count up numbers of items in various categories in a column. The formulae I have been using are like this: =COUNTIF(F$3:F$201, "Red") or where I've wanted to combine various comments =SUM(COUNTIF(F$3:F$201,"Yellow")+COUNTIF(F$3:F$201,"Cream")) I'm not sure what formulae to use to count up 1) the total number of entries in that column, so that I can make sure that I haven't missed some (without having to check manually!) 2) how to count up the values that do not match the other categories that I have specified in the COUNTIFs: this would be a value for finding how many 'other' entries there are in that column, without having to specify those values
View Replies!
View Related
Sumif With Or
I'm trying to do this: =SUMIF(B5:B12,OR("=D*","=?D*"),D5:D12) It returns zero each time. (BTW, =SUMIF(B5:B12,"=D*",D5:D12) and =SUMIF(B5:B12,"=?D*",D5:D12) both work just fine and return non zero sums.) So my question is, Where do I put the OR, or is there another way to do this?
View Replies!
View Related
Using SUMIF + AND (if Possible)
I'm working on a sheet where by I need to reference multiple columns of information to calculate a total. Such as (simplified example of my real issue): __A__B__C 1 20 AP1 C30 2 25 AP1 C30 3 35 AP2 E10 4 50 AP3 E10 My requrement is to only add values in column A wich have corresponding column B="AP1" AND column C="C30" I've tried the following and keep getting errors: =SUMIF(AND(C1:C4,"C30"),(B1:B4,"AP1")),A1:A4
View Replies!
View Related
Sumif
I am trying to sum the total of 10,000 lines in column B if column A is 0. I cannot get sumif to work. do I use a different formula?
View Replies!
View Related
Nested Countif?
I searched on this and didn't find what I was looking for. I want to count entries that have critieria I specify in two different ranges. Is countif the way to do this?
View Replies!
View Related
IF Or COUNTIF Formula?
See attached document, there are 11 cells in which will either contain Yes or No. Looking at the different combinations that there can be there can only ever be 9 out of the 11 cells being used or 10 out of 11 being used. Also the last question (Row 25) could be filled N/A if this occurs I would like the formula not to count that. Is there a counting formula or IF formula which can be done to help me out?
View Replies!
View Related
Countif Macro
In col A (from A2 and down), I want to run a Countif on a Range of concatenated values in Col B (B2 and down) I'm having trouble with the Countif part of my code Sub countifDataRange() Range("A2").Value = Range("B2") Dim LastRow As Long LastRow = Range("B" & Rows.Count).End(xlUp).Row With Range("A2:A" & LastRow) .Formula = "=COUNTIF(Range("B2:B" & LastRow), "B" & Rows.Count)" .Value = .Value End With End Sub
View Replies!
View Related
COUNTIF On Another Sheet
It seems I have run into yet another roadblock in my spreadsheet building. The issue is I would like to have a summary page of some of the information contained on my sheet1 of the workbook. Is there a way to use the countif function from my summary page and have it count the information on the Sheet1 which is nameed by the way (status report) I thought this formula would work but it doesn't. =COUNTIF('Status Report'!I11:I1958,"Ammo-com-1")
View Replies!
View Related
COUNTIF In AutoFilter..
I have the following type of data. How can i countif if the data in Filter Mode. DatesArea Code3-Oct-08 A4-Sep-08 A4-Sep-08 A4-Sep-08A7-Sep-08B4-Sep-08 B4-Sep-08 C4-Sep-08 D4-Sep-08 E12-Sep-08F13-Sep-08F4-Sep-08 A15-Sep-08A If i am filtering on 4-Sep-08, i want to count how many "A". I know a method Filter by date 4-Sep-08 and area code "A". But is there any formula without filtering two columns? I have a cell down TOTAL A = ???? [ ???? What is the formula can i use? ].
View Replies!
View Related
Countif Arguments
The 2 basic arguments of the Countif Function (range and criteria) are simple and make sense. However, I've observed instances where the criteria component is in fact a range. In this case, is what is the syntax instructing the app to count in the first range?
View Replies!
View Related
COUNTIF Not Working
I am using the following formula to count the total number of contract types if 'ITD $K' equals '0' zero. But it returns 0 as output. =SUMPRODUCT('PROCUREMENT CONTRACTS'!$E$5:$E$225="GROSS")*('PROCUREMENT CONTRACTS'!$J$5:$J$225=0)
View Replies!
View Related
COUNTIF With An INDIRECT
I need to have a cell count the number of cells that contain a certain text found in cell $b3. (the 3 is relative the B is not). The data will be found on multiple sheets called "Game x" where X is an integer. (Game 1, Game 2, etc...) the cells are between $b$65 and $b$73. i tried =COUNTIF(INDIRECT("'Game " & 1:$A$1 & "'!$b65:$b73"),$B3) but it did not work. also after i get that working I need to do the same thing, but i need to count the number of times the Name in $b3 appears in the List with the word "Win" in the "D" column (next to the $b65:b$73) Again i tried =COUNTIF(AND(INDIRECT("'Game " & 1:$A$1 & "'!$b65:$b73"),$B3),(INDIRECT("'Game " & 1:$A$1 & "'!$b65:$D73"),"Win"))
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 With Date
Ontvangstdatum21/08/2008AantalDatum@dagen oud22/08/200821/08/200825/08/200822/08/200825/08/200825/08/200825/08/200826/08/200825/08/200825/08/2008 In date: i have put a advanced filter with unique records. I would like to find a formula that counts howmany times that that date accurs in the first colom I tried with a countif but you can't put a cell valume in it. i thougth something like this: =countif(a:a;"=d") but ofcourse that doesn't work
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
Sumproduct Vs Countif
I have a worksheet where I am trying to count the number of occurences of several text strings. For example: I'm trying to count how many times "paid in full" and "fully paid" occur in column A. I have two formulas, and both seem to work, but since I don't really understand either of them, I'm wondering which I should use and how I would adapt it to include additional text strings. (Like adding "paid" to the list) Here are my formuals (I didn't write either of them, another co-worker did) =(COUNTIF(A:A,"paid in full"))+(COUNTIF(A:A,"fully paid")) =SUMPRODUCT(--(A1:A50={"paid in full","fully paid"})) Also, if there is another and easier way to do what I'm trying to do, I'd love to know.
View Replies!
View Related
Countif Function
I'm trying to do a count where column C="Employee" & column E="2008". Below is the formula I have tried and is obviously not working. =countif(and(C113:C143="Employee",E113:E143="2008")
View Replies!
View Related
Countif And Dates
I have a spreadsheet where I am tracking dollars spent for warranty claims. The information is put in, and a date for the claim is put in as well, which is then formatted to show the date. For example, if I type in 01/13, it shows up as 13-Jan. The date column spreads from D2 to D384. I would like to make a section that will go through the whole column and give me a total number of claims put in for jan, then in the next cell down the same thing for feb, etc etc. Basically it will be something like this: Claims per month: Jan 14 Feb 8 Etc, I have been trying to use wildcards for countif, such as "*-Jan", or even just "Jan" but it is not returning any result.
View Replies!
View Related
Countif Funtion For More Than One IF
If I have column A with a department name e.g. A, B, C, etc and column B with a date next to the some of the entries of column A. How would I compose a formula to return an answer of the amount of records that show column A as "A" and column B as "1". This is probably immensely simple and I have a wet fish on standby to hit myself with.
View Replies!
View Related
COUNTIF On Dates
I have two different worksheets. One has dates recorded as dd-mmm-yyyy (e.g., 28-Nov-2009). On the other, I have to create a lot (over 1000) of countifs against the months/years of these dates. So, for example, if there are several dates on the first workbook that fall in Nov-09, i want to do a countif on the second workbook that tells me how many Nov-09 dates there are.
View Replies!
View Related
Countif Across Sheets
I have one sheet with data and want to have the data transferred automatically into another sheet. Let's say, column A of Sheet1 contains information like A1 - F A2 - M A3 - F A4 - F A5 - M asf. In Sheet2 I want to have a cell in which, f.e. the sum of all Fs is added AND kept up to date whenever I alter the information in Sheet1. I've tried countif.3d and also sumproducts(countif(indirect...),
View Replies!
View Related
Countif Different Columns
I have 16 columns with 10 rows with different single digits in them. I want to count the number of times the number 2 appears in columns A, C, E, G, I, K, M, O (in other words every other column in this case). I know how to write the formula by using countif to find the results but it is rather long. The fomrula would look like this:
View Replies!
View Related
Countif In More Than One Range
Is ther any way around not being able to do this - I read that if u make the ranges an array it shoul work - Shift, Control, Enter - or something but I can get it to work. I was hoping to use copuntif for this :- COUNTIF(F33:F39,M33:M39,T33:T39,AA33:AA39,AH33:AH39,AO33:AO39,F67:F73,M67:M73,T67:T73,AA67:AA73,AH67 :AH73,"b")
View Replies!
View Related
Using AND/BETWEEN In A Countif Formula
my current formula is =COUNTIF('Input Page'!A2:A50000,"=Monday") i'd like to change it to check what day is in the field and then only do the above formula if that day is within the past week. so i need the "=Monday" section to be changed to read "(is equal to monday) and (is between today and today-6)" ...todays date will be taken from 'Input Page'!B2:B50000
View Replies!
View Related
Countif Without Range
I have a column with multiple data entries ( dates, amounts, percentages, etc). From these I want to count how many dates are after the selected date. But I am unable to pickup date cells selectively. i.e. counif ( {A1, A6, A11, A16, A21, A26}, >=1-Jan-2009) But the function is giving error as it only accepts ranges. I can use countif(A1:A26, >=1-Jan-2009) But the problem is some numerical values are also in the same range ( as the numerical format of dates) - so I am unable to use it.
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
|
TRACKER
