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 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
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
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
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
DSUM Formula Instead SUMIF Formula
I come across a spreadsheet that is using DSUM formula, I did not understand why someone used DSUM instead of SUMIF formula, because using DSUM formula they had to use two extra cells for criteria, as seen in this example. I like to know is there any advantage of using DSUM instead of SUMIF in this example. The formule they used is =DSUM(J109:K113,2,$M$2:$M$3) and the criteria is G,D so they are adding all Debits in that column. I changed the formula to =SUMIF($J$110:$J$113,"D",$K$110:$K$113) that of course does not need $M$2:$M$3 range criteria. G 3458224.37 D 3401317.50 C 3401317.50 C 56906.87 D 56906.87
View Replies!
View Related
Countif - Formula Too Long
I need to add more arguments to a countif formula, but I have maxed out the 1024 character limit. Is there another way of writing this formula or is there a way I can add more arguments to this formula. Formula is: =countif(A2:A9999,"AH")+countif(A2:A9999,"ARHC")+countif... I have a list of the arguments, can I do something like: =countif(A2:A9999,B2:B50) (with the list of arguments in columns B2 to B50)
View Replies!
View Related
Countif Formula Explanation
I came across this formula but could not for the heck of it understand what exactly does it do. I will really appreciate if some one can make me understand what this formula is intended to do. =Countif(data,data) where data is the named range..
View Replies!
View Related
Countif/sumproduct Formula
I have a sheet with repeated dates for several months and I need to break out data by week and then by certain criteria. I can do 1 or the other but combining the COUNTIF formula and the SUMPRODUCT formula has proven to be beyond me. I have this now: =SUMPRODUCT(--(D2:D31719"7/19/2008"+0),--(G2:G31719>"5")) but it returns a value of 0 which is incorrect. What I need to do is have the formula return a sum of all of the fields in colG that are >5 within a date range. Once I find that # I have to divide it by another field and multiply by 100 to get the percent.
View Replies!
View Related
Can You Use Countif In An Array Formula
Lets say I have 30 rows of numbers in two columns. I know I can multiply each number together in each row with an array formula ={A1:A30*B1:B30} Question: Given the same ranges, except no numbers but just the text 'x'. some rows have 1 x some have 2 some have none. Can I use 'Countif(A1:B1,"x")' in an array formula and if so how would that be structured?
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 Formula Which Shows A %
I already have a conditional format which is working OK. I would like to put a formula in the cell so it shows a % so currently it would be 100% as there is 3 cells below showing 3 greens. Alternativily each colour is worth a value - Green = 1, Amber = 2, Red = 3. So Cell I3 would show 3.
View Replies!
View Related
Countif Formula Not Working
I have a column of numbers ranging from 0000-9999. They are not in order and multiple numbers are used more than once. I would like to know how many times the numbers in the range of 4000-4999 appear. I am trying to use the formula =COUNTIF(F4:F500,"=4????") or =COUNTIF(F4:F500,"=4*") but neither are working. If I do a single number like this =COUNTIF(F4:F500,"=41420"), it will count them for me.
View Replies!
View Related
Sumif/product Formula
I'm trying to do what I think its a simple formula with Excel 03 but can't get it to work. The info is in two work sheets - A&B. [code]WORKSHEET A WORKSHEET B A COLUMN 1 COLUMN 2 COLUMN 3 COLUMN1 COLUMN 2 B BLANK BLANK 10 BLANK DATE:1/28/09 C NAME DATE WORKED 12 NAME D Jones 1/2/09 13 Bell E Smith 1/6/09 12 Smith F Cox 1/28/09 12 Cox G Cox 1/28/09 10 Collins H Collins 1/28/09 11 Jones In worksheet B, Column 2, I need to pull the SUM of COLUMN 3 IF THE NAMES IN BOTH MATCH AND THE DATES MATCH I've spent hours tyring to get this thing done!
View Replies!
View Related
Formula; SumIf And LEFT
I have columns A and B, and want to add values in column B based on corresponding criteria in column A. The criteria is if the first two characters in the string in column A = 15. The only way I can think to do this is with LEFT, but LEFT asks for a specific string, and I want to evaluate each cell in a column. So I could write =sumif(A:A,"LEFT(A:A, 2)=15",B:B) but of course that doesn't work.
View Replies!
View Related
SUMIF Formula To Ignore #N/A
All I believe Juan Pablo posed a quick little diddy on how to have the SUMIF ignore #N/A?? I promised myself I would remember it but here I am asking if anyone remembers it or know how to have Excel add up a column even though the column contains #N/A's.
View Replies!
View Related
Tweaking A SUMIF Formula
The following formula finds the lowest five values in row 6 from column H to W. However if there are less than five values in the row the formula returns 0. I need to tweak it to return the smallest 5 values weather there is one or a full row of 16. Also if there are any of the same values I still want only the five lowest.
View Replies!
View Related
Alternative For SUMIF Formula
The below formula is giving the desired result and everything seems fine. Need if there is an alternative formula which can give same result in more refined and simplified manner. =K28+SUMIF($E$3:$E$24,$E$7,$D$3:$D$24)/(K23)-SUMIF($B$3:$B$24,$B$8,$D$3:$D$24)/(K23). The formula is based on the following data: K28 = Last month balance Column E = Type of expenditure Column D = Values Column B = Payments K23 = Currency conversion rate
View Replies!
View Related
Sumif And Isblank Formula
Is there a way to use sumif on blank cells. That is I only want to add the contents of column Q provided the Column I corresponding rows are blank. I used this formula but it didnt work =sumif(I:I,ISBLANK,Q:Q)
View Replies!
View Related
Array Formula Using Two Columns And Countif
Having trouble getting Excel to compute a value based on two columns on another sheet. What I need it to do is see if column A has the value I specify, then look in column B and see if that date figure is greater than 180 days, and then count it. So I might have 45 that meet criteria A, but then need to find items in that subset that meet criteria B...
View Replies!
View Related
Countif Formula: Count Certain Information
I have a spreadsheet designed for softball schedule for an entire league and am trying to come up with a way to count certain information so that I can figure out if the schedule is balanced. What I'd like is for a way to sum the number of games each team plays on diamond 3 or 4 and to sum number of games at 6:30/7:30/8:30
View Replies!
View Related
Countif Formula: Count All The -01- Cards
I am not sure how to go about this but here is my problem: text in cells a40:A147 containing card number this: -01- and cells h40:h147 containing and "x" if the card -01- is closed. Now I want to count all the -01- cards that do not have an x in the h40:h147 cells and I need to count all the -01- cards that do not have an "x" in the h40:h147 cells.
View Replies!
View Related
Explain Index Sumif Formula
Can someone explain this formula to me -- and not the person who sent it to me please -- he confused me even more and I need to understand it so that I can learn for the future of how to apply it! =IF(AC$17<=0,0,SUMIF($B16:INDEX($B16:AA16,$AC17-4),"<>",$B17:INDEX($B17:AA17,$AC17-4))) So basically it is pulling aC17 -- if it less then zero, return zero. If not, SUM B16-? this is where I am lost.
View Replies!
View Related
Sumif - Resolving Text From A Formula
I have 2 workbooks linked; one contains raw data and the other is a summary of all the data sorted into months etc. The formula for linking the two workbooks checks a column containing text (months), and then sums the values of another column if for each different month. This works fine if my column with the month is hardcoded with the text valve e.g. sumif(Range,"MONTH",sum_range) and MONTH is Jan, Feb, Mar etc. However, i really want to automatically calculate the data within the month column but as soon as i put a formula in there it fails to complete the sumif calculation in the other workbook because it cant seem to resolve the text valve from the formula.... How do i get round this issue of the text being resolved instead of the formula?
View Replies!
View Related
SUMIF Formula For Values In Different Rows
I thought that SUMIF FUNCTION should work for what I am trying to achive but it does not look like. Here is the example: 1,2,3 etc. are rows and A, B,C - columns A B 1 Gain (Loss) on Balance sheet reval, 91105(2,035,611) 2 Expense 3 4 NET Gain (Loss) on LTD 945,600 5 including Hedge on $ million Revenue I need to write the formula in cell e.g. A8 that would look at my either "Expense" or "Revenue" and sum it if it is revenue or sumit if it is expense. I have the whole spreadsheet of rows and the label "revenue" or "expense" repeats almost always after 2 rows.
View Replies!
View Related
Sumif Formula Not Summing And Counting
I am looking to effectively complete a sumif formula but not summing, counting. I thought a countif would be a simple solution but the countif is different i.e. SUMIF (range,criteria,[sum_range]) COUNTIF(range,criteria) I want to be able to put =countif(b1:b10,A1,c1:c10) as you would with a sumif but I am told i have put too many arguements so what shold i be using??
View Replies!
View Related
|
TRACKER
