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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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 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
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
SUMIF Date / Average
I have faced with a problem with Excel when I wanted to find the average for a stock price. I have 2 columns: 1st Column: Dates from 1st May 2007 till 23th December 2008 (Rows:A3:A403) 2nd Column: Stock prices from 1st May 2007 till 23th December 2008 (Rows:B3:B403)
View Replies!
View Related
Lookup/Sumif/Average
I have data columns A-C example: Material Year/MonthHeight in cm 1000000006200902114.00 1000000006200902120.00 1000000006200902110.00 1000000007200903107.00 1000000008200901115.00 1000000008200901111.00 1000000008200903117.00 and over about 1000 rows. On sheet 2, i have list of material numbers (about 60 in total) what i need is a formula that will lookup each material number in the long list and give me the average height for a particular month. i.e. in example material 1000000006 average for period 200902 = (114+120+110)/3 =114.67 so i'd end up with on sheet 2 columns A-F with headings below with relevant formula for each month. Material-Jan-Feb-Mar-Apr_may
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
Formula =AVERAGE(B16:L16) To Give The Average
I'm using the formula =AVERAGE(B16:L16) to give me the average. However I have a couple of problems with this. Firstly I would like to exclude the value zero from the average. Secondly to also ignore the lowest and highest values. Example, if the values in the cells are 0,1,2,3,4,5,6,7,8,9,10 then the current result shows 5, by ignoring the 0 and lowest value 1 and highest value 10 the average should be 4.5.
View Replies!
View Related
Grade Point Average Using AVERAGE IF AND
I am attempting to calculate Grade point averages for my students for all classes. There are 5 columns of grades to be considered. I frist assign values of 0-5 to the grades then average the points. My problem is that I want the AVERAGE to ignore the zero but it calculates it as well. (I enter a 0 if I have no grade for that class.) I have tried the{ } to make it an array formula but this also did not work. Here is what I have, can anyone help? =SUM(AVERAGE(IF(AND(L2>89,L2<100),5,IF(AND(L2<90,L2>79),4,IF(AND(L2>69,L2<80),3,IF(AND(L2<70,L2>59), 2,IF(AND(L2>0,L2<60),1,0))))),IF(AND(T2>89,T2<100),5,IF(AND(T2<90,T2>79),4,IF(AND(T2>69,T2<80),3,IF( AND(T2<70,T2>59),2,IF(AND(T2>0,T2<60),1,0))))),IF(AND(AB2>89,AB2<100),5,IF(AND(AB2<90,AB2>79),4,IF(A ND(AB2>69,AB2<80),3,IF(AND(AB2<70,AB2>59),2,IF(AND(AB2>0,AB2<60),1,0))))),IF(AND(AJ2>89,AJ2<100),5,I F(AND(AJ2<90,AJ2>79),4,IF(AND(AJ2>69,AJ2<80),3,IF(AND(AJ2<70,AJ2>59),2,IF(AND(AJ2>0,AJ2<60),1,0))))) ,IF(AND(AR2>89,AR2<100),5,IF(AND(AR2<90,AR2>79),4,IF(AND(AR2>69,AR2<80),3,IF(AND(AR2<70,AR2>59),2,IF (AND(AR2>0,AR2<60),1,0)))))),-1) I know it is huge. The syntax is correct. It calculates the average, but always for all 5 columns. It will not ignore a 0 in a column.
View Replies!
View Related
Avoiding Average Of An Average: Times
Ok, I for some reason just cant wrap my head around this. I need to to get the average time per call of two rows, but they are based on how many calls taken. so in one column i have 50168 calls taken at 4:21 seconds per call. and in the next row i have 597 calls taken at 5:20 per call. I need to see what the new average will be with them combined, and I need it to display in m:ss form.
View Replies!
View Related
Making Average Buy Price And Average Sell Price
to formulate Excel formulas to obtain the average buy price and average sell price for me to do this futures trading. Thanks a lot. I downloaded the Htmlmaker to post the spreadsheet here to show the manual way to calcualte the average buy price and average sell price but when it is on html form, i clicked on the 'Please click this button to send the source into clipboard' button & then i paste into this thread. Is the way to make my spreadsheet appear here correct cause it cannot work.
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
|
TRACKER
