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")
I play poker, and I have a live session log in an excel spreadsheet, like so:
View 2 Replies View RelatedI 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.
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.
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
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.
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'
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 5 Replies View RelatedI 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..
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?
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)
I am trying to produce a report of supplier transactions sorted by area code. I have a spreadsheet of data consisting of Supplier Code, Supplier Name, Transaction Amount and Area Code.
I want to be able to firstly seperate the transactions by area, then also consolidate the data so it shows one row per supplier with total amount spent on that supplier and a transaction count on that supplier. I know how to sumif the transaction total and countif the transaction count.
However I have problems consolidating the suppliers in to one row per unique supplier and I also have problems nesting an AND statement in to the sumif/countif statements. I need additional criteria in the forumula to only count transactions in a specified area code.
I've attached an example spreadsheet to make it alot clearer. See results sheet in this workbook.
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))
It's actually a sumif/countif that works fine if i sort my table, which i don't want to do.
Here's the original formula:
Code:
(SUMIF(H4:H35,"1",G4:G35)/COUNTIF(H4:H35,"1")*1+SUMIF(H4:H35,"2",G4:G35)
/COUNTIF(H4:H35,"2")*2+SUMIF(H4:H35,"3",G4:G35)
/COUNTIF(H4:H35,"3")*3+SUMIF(H4:H35,"4",G4:G35)
/COUNTIF(H4:H35,"4")*4)/10
I'm trying to apply the formula using some other filters, like if(C4:C35=O5 & I4:I35=M5).
I tried creating a SUMPRODUCT out of it but it was just as bad:
Code:
=(SUMPRODUCT((C4:C35=$O$5)*(I4:I35=$M$5)*(H4:H35=1)*(G4:G35))
/SUMPRODUCT((C4:C35=$O$5)*(H4:H35=1)*(I4:I35=$M$5)*1)*1+...
And also this with sum(if)
Code:
=(SUM(IF(C4:C35=$O$5,IF(I4:I35=$M$5,IF(H4:H35=1,G4:G35))))
/COUNT(IF(C4:C35=$O$5,IF(I4:I35=$M$5,IF(H4:H35=1,G4:G35)))))*1+...
Is there a shorter formula that I'm not seeing ? I'm also confronted with the need of adding an "ISERROR" in the formula because not all ranges have the 1-4 numbers in column H.
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 9 Replies View RelatedI 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 9 Replies View RelatedI 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.
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.
I am wanting to use these two formulas in one cell. Is there anyway to do this? If "AD3" is 0 I want this =SUM(X3:AC3) and then if cell "AD3" is greater than 0 I want to basically use this formula
=SUM(AH3,X3:AC3)-AD3.
Is there anyway to merge these two formula's?
I have a simple database spread sheet and I need to count a column under certain conditions. In one column I have employee names that appear repeatedly, in another I have codes. I want to be able to count how many times the code appears next to the name.
For instance:
If b4:b65000 = Sam Douglas then I want to count how many times different codes appear in the adjacent cell.
Sam Douglas:BI
Sam Douglas:BI
Sam Douglas:SI
Sam Douglas:BI
BI = 3
SI = 1
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
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?
I have a workbook which I am trying to clean up and make more efficient (in terms of formula calculating time).
In it are examples of array formula such as this one (which work correctly):
{=SUM(IF(Expected!$A7:$A894=5,Expected!$K7:$O894))/3}
From past readings here, I have heard that the array {SUM(IF(...} is slower in calculating than the SUMIF(...) equivalent.
As such i tried to convert is to:
=SUMIF(Expected!$A7:$A894,5,Expected!$K7:$O894)/3
This gives a different answer though. I thought for a single criteria they would be equivalent.
Q1: What am I doing incorrectly above?
Q2: Is there a more efficient version of the array formula I could use (SUMIF and otherwise), so I can have some options availible.
I got a column A containing a alpha-numeric data and want to sum up the numeric value of each cell which begins with "t" and have the sum divided by 486 (i.e. (120+230+25)/486). I tried the following formula but to no avail.
A
t120
t230
m45
m30
t25
My formular: sumif(mid(a1:a5,2,3,">0"))
I have worksheet of data. I have another worksheet that is used just for a summary. This data is mapped to a "Job number" (1123646, 1256974, etc.) The first two numbers determine what area it is referring to.
Each column represents a different amount (month to date cost, month to date profit, year to date cost, year to date proift, etc.) I need to summarize each column for each area and display the result on the other worksheet.
I can't use exact ranges like = sum(a9:a31) b/c that range will change each time the worksheet is used. I thought maybe using a loop, but then I got stuck and don't know where to go from here. Here is what I have so far.
Sub SumProfit()
Dim C As Range, sh1 As Object
Application. ScreenUpdating = False
Set sh1 = Sheets("JB-PRF")
Set C = Range("A9")
I don't want to abuse LOOPING or VBA for that matter, but I don't know of any other way to accomplish this seeing as how the ranges will not be the same each time. the shee that the data should be extracted to is named SUM-PRF
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 6 Replies View RelatedI 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
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
I know this is a dumb question but I can't get a sumif to work in vba. I want the actual formula in the cell, not just the output value.
This is my most recent attempt:
VB: Range("C2").Formula = "=SUMIF(Z7:Z12000, >0,N7:N12000)"
I get Run-time error 1004, application defined or object defined error.
Why doesn't this work?
I have this from FDIBBINS working if data is on column D and E
=COUNTIF($E$2:$E$4,A2)
How would the same count function if there is a column between d and E
as attached ?