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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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
VLOOKUP Or SUMIF: Find The Line Staus Of Each Product In Production Against The Order
I have a very large spreadsheet where i have orders, product and line status listed. I neeed to find the line staus of each product in production against the order. I'm using an array sumif formula but since the spreadsheet reachs 10000 rows of information I dosn't alway calculate correctly. ORDERPRODUCTSTATUS ABCDAPPLEPICKED ABCDORANGEON HOLD ABCDPEACHSHORT ABCDYAMSSTAGED EFGHAPPLEPICKED EFGHORANGEON HOLD EFGHPEACHSHORT EFGHYAMSSTAGED Required Result Order ABCDAPPLEORANGEPEACHYAMS PICKEDON HOLDSHORTSTAGED
View Replies!
View Related
Sum The Product Formula
I have a Sumproduct formula that should be resulting in 0. However, it is resulting in a very small number. I have 2 columns of data. The first one contains -169.5, -123.4, -34, -67.4, 169.5, 123.4, 34, and 67.4. The second column contains all 1's. When I create the following formula, =SUMPRODUCT(H27:H35,J27:J35), it results in 5.68434E-14. Sometimes, when I have different numbers in the first column, but it still adds up to 0, the Sumproduct works.
View Replies!
View Related
Sum Product Formula
What I need to do is count the number of cases and values of everything written between the first of January and today (242). I know how to do it by counting the days from the 1st of Jan to today but how can I do it so it automatically updates each day. This is what I have at the moment. The product name is in K, the date is in H and the value is in M. The range is from cell 7 to cell 94 =SUMPRODUCT(--($K$7:$K$94="Pension"),--($H$7:$H$94<=TODAY()),--($H$7:$H$94>=TODAY()-242),$M$7:$M$94)
View Replies!
View Related
Sum Product Funtion Within A Date
I have a column that lists names, and I need to count how many there are for each name, sounds easy enough use the following i think. =SUMPRODUCT((A1:A10="Joe") The trick is, I only want it to sum them if it's within the current month. I am entering something wrong because I get an error message.
View Replies!
View Related
Sum Product Formula With Criteria
The following formula takes a census of people in our area by date and by hour (sample data attached): =SUMPRODUCT(--(FLOOR(Data!$B$4:$B$39,1/24)<=$B2+C$1)*(Data!$C$4:$C$39>=$B2+C$1)) The formula works great, but I would like to add another component whereby the census will only count someone if they have a certain description (see column A - with color descriptions). So how can I setup the formula, to say, only look at the "red" colors during the same specified dates/times and give me the same type of census...instead of looking at all of the data or creating 50 different ranges for all the colors?
View Replies!
View Related
Sum Product Not Refreshing On Opening
One of my SUMPRODUCT formulae doesn't seem to be refreshing when i re-open the excel file. =SUMPRODUCT(--(Data!$B$7:$B$2000="FG"),--(TEXT(Data!$F$7:$F$2000,"mmmm")=A26),Data!$M$7:$M$2000) If i open the excel all the cells with this similar formula show as 0. If i press Shift+Ctrl+Alt+F9 then they calculate. I have another SUMPRODUCT formula on another worksheet in the same book that seems to be fine. =SUMPRODUCT(--(Data!$B$7:$B$2000="FG"),--(Data!$E$7:$E$2000=B7),Data!$M$7:$M$2000) I can only assume that the reason why it's not calculating automatically is to do with the TEXT "mmmm" reference, is that correct? Is there anyway i can have it autocalculate so that if i send this as a report i don't have to ask the ppl looking at it to please press Shift+Ctrl+Alt+F9 when they open it :P
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
Sum Only Rows Where The Product Begins With Letter
I have a sheet that has a list of products in column A. These all begin with either an "H" or a "S" The following columns are sales values/pcm. I would like to sum each row based on the first letter of coumn A. I have tred using the left function to deternmin the letter but I am not sure how to then sum that row accordingly.
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
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
Sum Product To Count Data In A Date List
I have been using the sumproduct formula to count how many times a certain piece of data appears...which works fine...for text purposes such as names, initials etc... I now need to amend the formula to count how many times a date appears in a range of dates. I know when you use the sumproduct...dates must be input and formatted as text. example A1 01-Jan 02-Jan 5-Feb 7-Feb 12-Feb formula to only count dates with feb in?
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
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
Conditional PRODUCT Function Formula (return The Product Of A List Of Numbers In A Column)
I am trying to use a PRODUCT formula to return the product of a list of numbers in a column, between 2 specified dates. The spreadsheet is arranged as follows: Column BA list of dates Column C & DNumerical data not used in any calculations Column EThe numbers to multiply together Cell A1Start date to be used in the PRODUCTformula Cell A2End date to be used in the PRODUCT formula The formula I have to multiply the data in column E, subject to the start and end dates in Column A is: {=PRODUCT(IF(B15:B1000>A1,IF(B15:B1000<=A2,INDEX(B15:E1000,0,4))))} This returns zero though works appropriately if I replace “PRODUCT” with a “SUM”.
View Replies!
View Related
Dynamic Named Range For The Product Name On The Product Details Sheet
I'm working on a order spreadsheet system, and I have one sheet called Product Details, where the product name, list price and product code are found, these link to the Sales order page, and I need them so that they can be added to. So far, I have created a dynamic named range for the Product name on the Product details sheet, and linked the the range via a list validation on the Sales order sheet. The drop down list displays the products and can be added to by typing new product names on the other sheet. What I need now is that when a product on the sales order page is selected, it draws the the list price and product code data automatically from the sheet, I tried using a normal vlookup, but I couldn't get it to work. I also need the list price and product code columns to be 'dynamic' so new values can be added further along.
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
Sum Up The Totals Of 'quantity X Unit Price' For Each Product
i am currently working on cost analysis of large projects. I have a worksheet with about 100 headings and i would like to sum up the totals of 'quantity x unit price' for each product. I tried the sumproduct function but it did not work and i had to do the '=sum(b4*c4,d4*e4,f4*g4,..........) to get the result in the total column. I attach a small sample of the file.
View Replies!
View Related
{SUM(IF(...} To SUMIF(...)
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.
View Replies!
View Related
Sum Multiple Product For Multiple Values
is it possible to use the sum product formula to give me the total results for multiple values? I know I can ask it to look at column a for a value of e.g "44" and column B a value of "gin2334" but can I ask it to ge me total for values from column A that equal "44, 45, 46, 47" ans B "gin2334"? if not has any got a clue as to how i would get these results?
View Replies!
View Related
Sum And Countif From Two Cells
I have a formula =IF(AND(F14=3,G14<2)=TRUE,1,"") which is in cell J14. I also want to 'add' to this formula an 'or statement' (I am not sure of the terminology, so taking a shot in the dark). So it would continue to complete the calculation as normal, but if there is a '1' in K14 it would also output in J14 as a 1
View Replies!
View Related
Sumif: Sum A Range
I have come across this SUMIF formula which sum a range and ignoring errors produced by formulas in the range, I can't figure out how this works and need the expertise of the Excel experts here. =SUMIF(A2:A9,"<1E100"). What does "<1E100" represent and how its work
View Replies!
View Related
SUMIF Sum All Data That Meets 1 Or 2
Suppose I have some data with a code for each data point: 1 100 1 200 1 300 2 400 2 500 3 600 The first column is the code and the second is the data. I can use a SUMIF statement to sum all the data that have a certain code (like 1). What if I wanted to sum all data that meets one of a number of codes? Suppose I wanted to sum all data that meets 1 or 2. I know I can do this with 2 separate SUMIFs, but I was wondering if there was a way to do it with one.
View Replies!
View Related
Trying To Do SUMIF: Sum The Values In Row E In Sheet 2
I am thinking I need a sumif formula but having difficulty pulling data into sheet. The facts.... Sheet 1 (Summary Model): Location where I want to dump data Sheet 2 ('Phys Demand Estimates_0-17'): Location where data exists =IF('Phys Demand Estimates_0-17'!A2:A953='Summary Model'!B2,IF('Phys Demand Estimates_0-17'!C2:C953='Summary Model'!A5,SUM('Phys Demand Estimates_0-17'!E2:E953),0)) Translated...."IF in Sheet 2 looking up duplicate fields of Value1 = same value in list box on Sheet 1, and if in Sheet 2 looking up duplicate fields of Value2 in static list in Sheet 1, sum the values in Row E in Sheet 2.
View Replies!
View Related
Conditional Sum, Sumif/validation - Range
I have been trying to figure this out for days and have searched on all the postings and cannot find my answer. I am working off of two worksheets. Sheet #1 has 3 columns. Column A "Type of Bar", Column B "Type of Activation", Column C "Amount Spent". Column A has 2 drop down boxes, Pub & Nightclub. Column B has 2 drop down boxes, Sampling & Training. Sheet #2 is is a tracker that is going to add up certain criteria off of Sheet 1. For example, I need to get the sum of All Pubs and Sampling. I also need to track All Pubs and Training, Nightclub and Sampling and Nightclub and Training. I have tried the SUMIF, IF, and Conditional Sum. I keep running into a RANGE error.
View Replies!
View Related
Sum Data Into Buckets .. Multicriteria Sumif
I have date in column A, vendor in column B, and amount in column C. How can I summarize data into table that add the amount by vendor by month and year? I was thinking of doing sumif but I don't know how to specify a range of date (for example, 1/1/2005 thru 1/31/2005) to sum the data together. I am attaching an small example worksheet to help explain better.
View Replies!
View Related
Countif Function In A Macro: SUM And COUNTA Works
I'm using the SUM, COUNTA and COUNTIF functions in a macro. The SUM and COUNTA works but the COUNTIF function does not return results. Sub B_Test() Dim myRange Dim Results Dim Run As Long myRange = Workbooks(1).Worksheets("Master").Range("S6", Range("S6").End(xlDown)) Range("M3") = Application.WorksheetFunction.Sum(myRange) myRange = Workbooks(1).Worksheets("Master").Range("D6", Range("D6").End(xlDown)) Range("D3") = Application.WorksheetFunction.CountA(myRange) End Sub I have tried countless ways to rewrite the COUNTIF line with no results or compiler errors returned. Originally had problems with the SUM and COUNTIF function and found that column formating was the problem. After clearing all column formats, the SUM function promptly began working but the COUNTIF keeps eluding all my efforts. The column which the COUNTIF is pointed to contains values of 0 to 500. Only values greater than 0 are to be counted.
View Replies!
View Related
SumIf The Sum Of Unique Values Is Greater Than Criteria
I have a problem which is beyond my capabilities. I need excel to sum amount column (See example table below) by unique values in company column then conditionally sum those values based on the year of investment, stage of company and finally, the kicker, the summed Amount by unique companies value has to be less than a certain threshold. For example, I want companies in Stage Column of Exit only and in year 2004 only BUT only if the sum of the Amounts by unique company values is less than 100. So in the example table this would return 0. I have code and criteria written to sum unique values based on similar criteria such as sum if company Stage is Seed and Year is 2004 but for the life of me I can't seem to make the jump further. For Unique values I used this function: =SUMPRODUCT(($B$2:$B2=Year04)*($D$2:$D2=StageS)*($A$2:$A2=$A2))=1 Then: =DCOUNTA($A$1:$D9999,2, Criteria) or =DSUM($A$1:$D9999,2, Criteria) This along with criteria explained before returns the number of unique companies that are Stage=Seed and Year=2004 or sum of amounts with that criteria. I am trying to adapt this technique to get what I want but to this point have been unsuccessful. My best guess is that I will need to create an array of the unique companies (New Sheet column A) with the Summed Amounts (New sheet column B) and then run functions again with the easier equations and criteria? I am trying to avoid combining company records as each investment needs to remain individual and there are 16000 records. Any help would be greatly appreciated. Company Year Amount Stage XYZ 2004 10 Seed XYZ 2004 20 Seed ABC 2004 5 Early ABC 2004 25 Early DEV 2004 14 Later DEV 2004 19 Later TRU 2004 100 Exit TRU 2004 120 Exit TRU 2004 100 Exit
View Replies!
View Related
Using SUMIF To Calculate SUM Over 30, 90, 365 Day Period
I have a logbook which already uses a number of SUMIF formulas but have been trying to create one so that totals the number of hours flown in the above mentioned periods. I am attempting to copy the formula and then modify for the relevant column in this case: =SUM(IF(FlightDates<(TODAY()-90), 0, FlightLength)) FlightDates is the named range of the calender date in column A and FlightLength is the named range for total flying in column Z. However even though this forumla works in another situation e.g. tracking number of landings in the last 90 days, it comes up with an error.
View Replies!
View Related
Sumif Multiple Criteria: Sum Spend In Certain Categories
I have a column which lists total spend by vendor. I am using the sumif formulae to sum spend in certain categories, for example spend greater than £500,000. I would like to use two criteria, for example greater than £300,00 but less than £499,999. I am also using countif to get the number of vendors in each range of spend. How do I adapt my current code of "=SUMIF(D2:D1463,">300000",D2:D1463)" to look at greater than £300,000 but less than £499,999. I'm sure this is simple but like most things only simple if you know how.
View Replies!
View Related
Nested SUM & COUNTIF Formula With #DIV/0! Error
I have a student grade worksheet with this formula and it returns # DIV/0 which I need to solve the problem. I have tried many different ways and none seem to work. Con someone assist with this problem. Here is the formula: = SUM(R13,R55,R97,R139,R181)/(COUNTIF(R13,">0")+(COUNTIF(R55,">0")+(COUNTIF(R97,">0")+(COUNTIF(R139,">0")+(COUNTIF(R181,">0"))))))
View Replies!
View Related
Using Multiple Sum Ranges In Sumproduct() & Countif() In Array
My problem is : 1.In G Column I put logic for Fail and Obtained Marks. G2=IF(COUNTIF(B2:F2,">=60")=5,SUM(B2:F2),"Fail") 2. Now in H column I want use this formula which I obtained from this forum H2=SUMPRODUCT((G$2:G$7>G2)/COUNTIF(G$2:G$7,G$2:G$7&""))+1 To get the position of Students. But the text value "fail" in the G2:G7 getting Position No. 1 and i've noticed the reason by using evaluate formula as well. 3. I got solution by changing "Fail" with 0 by creating column I and then column H put this formula ........
View Replies!
View Related
Using Multipel Sum Ranges In Sumproduct() & Countif In Array
I want to get students positions in a class like 1st, 2nd, 3rd, etc. There can be two 1st position and 2 2nd positions at a time . 1.In G Column I put logic for Fail and Obtained Marks. G2=IF(COUNTIF(B2:F2,">=60")=5,SUM(B2:F2),"Fail") 2. Then i made array formula in column H to get the position ={SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G2))/COUNTIF(G$2:G$7,G$2:G$7&""))+1} Some improvement to write fail & position ={IF(COUNTIF(B3:F3,">=60")5,"Fail",SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G3))/COUNTIF(G$2:G$7,G$2:G$7&""))+1)} And more work to get total marks & position and Fail at the same time...... ={IF(COUNTIF(B2:F2,">=60")5,"Fail",SUM(B2:F2)&"-"&SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G2))/COUNTIF(G$2:G$7,G$2:G$7&""))+1)} Now I wounder that is there a way to do all calculations in column H without using column G By replacing (if there is any method) G2, G3, G4 etc. with Sum(B2:F2); Sum(B3:F3);Sum(B4:F4) etc. in below formula ={SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G2))/COUNTIF(G$2:G$7,G$2:G$7&""))+1}.....................
View Replies!
View Related
|
TRACKER
