I am trying in vain to use sumif and countif formulas with reference cells in the criteria. For example, in the formula =SUMIF(D5:DS5,"<A10",D8:DS8), I have to plug in the acutal value of A10 to get the calculation to work (=SUMIF(D5:DS5,"<9180",D8:DS8). I would like to use the reference cell (A10) all the time, instead of having to change the formula manually every time. Not sure where I am going wrong. Thank you, Gene SORRY - NEW TO THIS BOARD - ONCE I POST THIS QUESTION, I SAW SIMILAR QUESTIONS ALREADY ANSWERED. PLEASE DISREGARD THIS POST. THANK YOU &qu...

I need to calculate how many same products are chosen in a table from validated data to show a tally (using 2 sheets, 1 v/data & Tally List [Sales Sheet] and 1 v/lookup [Data Sheet]). I've tried SUMIF and COUNTIF but didn't seem to work at all on the v/data sheet unless I stuffed up, but where? How do i calculate how many "Blue" items are listed each time a Blue item is added to the Sales Sheet via combo/lists? Each time a new item is listed, it will automatically appear in the tally list as a new item and/or count tally of appearences sorted by ranking. There are ...

Hi, i have a column that joins a series of other columns to creat a unque key for a table data I have in a sheet. when I then use sumif or countif against the concatenated column it says there is more than one the same and the sumif produces the same total for a number of lines. visually looking at the unique key column, I can clearly see they are different. ideas? Simon Shaw Are you sure the formula is updating? ******************* ~Anne Troy www.OfficeArticles.com "Simon Shaw" <simonATsimonstoolsDOTcom> wrote in message news:9471AC5F-A9AA-43CB-86B4-B08B088CA3CF@mic...

small example, we need to count employees for SICK DAY ( S ) as 1 point, and a late or TARDY ( T ) as .5 point. A1 = S B1 = T it should total to 1.5 I tried to use: =COUNTIF(A1:B1, "S" +1) ( equals 1 ) and I tried: =COUNTIF(A1:B1, "T" + .5 ) ( equals .5 ) But neither worked.....I also tried the SUMIF but no luck..... Any Ideas??? Thanks so much, this is a great group! You can use the following formula to total the S and T values: =COUNTIF(A1:B1,"S")+COUNTIF(A1:B1,"T")*0.5 Ange...

I would like to count the number of records that appear in the same column for example column "A" A hat and coat coat hat hat and coat gloves The formula that i am looking for would answer the question, how many records contain ("hat and coat" and "hat") the answer would be 3 records. Any help would be greatly appreciated! -- Cheers =SUMIF(A:A,"*hat*") -- HTH RP (remove nothere from the email address if mailing direct) "zubee" <zubee@discussions.microsoft.com> wrote in message news:60ABC3EF-9474-403C-BDB9-8091EC034425@micr...

Hi folks: I have a Bill-of-Materials where the parts used in a product are referenced like C11, R22, etc. The contents of C2 are: C13,C16,C18,C19,C23 To get a quantity of the parts used of each type, is it possible to count the occurences of "C" (or R, or anything else) ? I've tried =COUNTIFSheet1!,"*C*" and variants Would appriciate any help - Many thanks Steve Have you tried =COUNTIF(Sheet1!A:A,"*C*") where A is the column with the data in -- HTH RP (remove nothere from the email address if mailing direct) "s boak" <sboak.e...

I'm not certain if this a possibilty, but I am not able to figure it out on my own. I am trying to count cells (with text only) in one worksheet to have the total appear in another sheet, same book. I want the total of how many times ReqDept (A2:A6) appears for each Mod (B2:B6). For instance how many times MR & GYN match =2; MR & ENT =1. What would be the correct formula to write? Example ............A.........B 1 ReqDept Mod 2 MR GYN 3 CT ENT 4 MR ENT 5 MR GYN 6 US PMR -- Thanks, Bindy Bindy, Try ...

Hi, I have been playing with OpenOffice 2.0 Calc and found that it had additional criteria in the COUNTIF and SUMIF functions, such that it is possible to do a COUNTIF or SUMIF using a regular expression as a criteria e.g. If I wanted to count all cells in column B that started with the word TRANSFER my COUNTIF statement would look like this: =COUNTIF(B2:B20,"TRANFER.*) Can anyone here tell me how it would be possible to achieve the same results in Excel, please? -- Cain Didn't you get this answered yesterday? =COUNTIF(B2:B20,"TRANsFER*) (spelling error corrected, too.) ...

We are using a spreadsheet to log deals executed. Some of these ar bulk deals and are marked as such on the spreadsheet. I need to count how many bulk deals we have carried out, which I can d using CountIf and then I need to count how many of the bulk deals wer over �10,000 and this is where I am struggling. The CountIf formul only counts one range (which I am using to count the actual number o bulk deals) while the If formula only looks at one cell at a time which isn't what I want. Has anyone any ideas? Many thanks -- Angel16 -------------------------------------------------------...

I need to count the rows only if these two conditions exist in the same row. Column A must be a specific numerical value and column B must contain any text. Thanks... I assumed your range is row 1:100, change to fit your data: =SUMPRODUCT(--(A1:A100=your_numerical_value),--(ISTEXT(B1:B100))) -- Regards, Dave "Vegs" wrote: > I need to count the rows only if these two conditions exist in the same row. > Column A must be a specific numerical value and column B must contain any > text. > Thanks... ...

I am working on sorting through a database of medical CPT codes and would like to get the total number of proocedure codes with the total amount of reimbursement for each code. If been able to use countif and sumif to get the totals but there are mutiple cases where there are mutiple reimbursements for a given date so the number of codes is skewed high. I would like to total the code and reimbursement ONLY if the name, date, and code all match: name date code $ jane 3/1/2009 99204 50 joe 3/1/2009 99204 25 jane 3/1/2009 99204 30 joe 3/2/2009 31235 150 mark 3/3/2009 31235 140...

Alex, This returns #Value. I don't think I need to sum the number of bul deals - what I am looking to do is count how many of the bulk deals w have done are over �10,000 -- Angel16 ----------------------------------------------------------------------- Angel160's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1550 View this thread: http://www.excelforum.com/showthread.php?threadid=27494 Oh dear! I used the following data to model your query: Bulk �4,000 Bulk �60,000 Non Bulk �1,000 �15,000 Bulk �10,050 =SUMPRODUCT((A1:A101="Bulk")*(B1:B101&g...

I need to combine sumif and countif. I am trying to add up data in a an area say D1:O256 if it matches a criteria. Thats easy. Sumif. Then I want to divide that number and find the average of the sum. So I would sumif to get the total and sumifcountif the same area if the cell is greater than 1. Kinda confusing. Basic example below but I need to to be sum if and an additional "if" in countif. So the countif needs to be if it matches the requirements from the sumif. Need some help here. Probably an easy fix. Just lost =(SUMIF(QA!C3:O252,A3,QA!D3:O252))/(COUNTIF(D3:O3,&q...

I have a spreadsheet of workorders showing various fields. In the report i can count the amount of jobs overdue by 30 days. I can do a count of how many jobs are priority 1. However i don't know how to right a formula that is going tell me how many priority one jobs are overdue by 30 days. It seems easy but I don't know. For eg. Priority ......... Age 1 23 1 35 I have attempted a few formula such as =sumif(sheet!$:$,1,sheet!$:$)-countif(sheet!$:$,">30") som...

Hi We are trying to do something similar to CountIF or SUMIF but we need the criteria to reference a cell rather than a set number. Is this possible and how? Thanks in advance! Hi use =COUNTIF(A1:A100,B1) -- Regards Frank Kabel Frankfurt, Germany "Lady Layla" <nospam@nowhere.now> schrieb im Newsbeitrag news:O41ZKWgsEHA.2948@TK2MSFTNGP12.phx.gbl... > Hi > > We are trying to do something similar to CountIF or SUMIF but we need the > criteria to reference a cell rather than a set number. Is this possible and > how? > > > Thanks in advance! > &...

I want to get the averages of data points that fit a certain criteria. I have a column of data that includes a bank's asset size and anothe column that indicates how much was paid to acquire the bank. my question is this, suppose i want to make a summary page that show the average price paid for those banks with say $100 - $200 million i assets, then an average for those banks with between $200 - $40 million, then an average for those banks with between $600 - $800 o something the key is, when using SUMIF and COUNTIF, how can I apply a range o values? I am familiar with using SUMIF to ...

dear all, can anyone help? if the cell A0-A100 is an array of percentage number, and i would like to have a function at cell A102 in order to calculation the number of percentage between 20%-30%, how can i do that? thank you. baf Baf, How bout: =SUMPRODUCT((A1:A100>=20%)*(A1:A100<=30%)) Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Baffin Shea" <baffin@shea.com> wrote in message news:3fef8e27@newsgate.hknet.com... > dear all, > > can anyone help? if the cell A0-A100 is an array of percentage numbe...

Our company gives their workers 3 paid Sick Days a year. at the end of the year, any left over days, are paid out. My question is: in cell A1, I have 1 ( 1 Sick day taken ) Balance should be 2. cell b1: =countif(a1,">0, <4" sum ) also tried the sumif: b1 =sumif(a1,">0,<4" sum) what am I doing wrong? Thanks for all the help! Great Group ! try =min(a1,4) -- Don Guillett SalesAid Software donaldb@281.com "Angela" <mehere@nowhere.com> wrote in message news:ee6uk099gfo70idfd2k6u58552r8arvfek@4ax.com... > Our co...

Windows XP Professional Office 2000 I have a worksheet in which daily data is collected. Each row is indexed by the date. I need to get the average for several indices based on the day of the week, i.e. average shipment count for all Mondays, Tuesdays, etc. I thought about doing a "sumif" statement then dividing by a "countif" statement, but I'm not sure how the syntax would work for the criteria portion of these functions. Any ideas on how to accomplish my objective?? Thanks in advance for any help you can provide! Rita Palazzi Senior Engineer / GTS Int...

Hi everyone. I have columns of numbers. 1 1 7 2 1 1 1 3 5 1 1 1 1 1 2 The spaces represent spacers between clients. What I'd like to do is find out how many #1's there are, how many are under 10, how many are under 20 and how many are under 30. Using CountIf(Range, "<1") works until it finds 11 or any numeral with a 1 in it. I come across this problem and not sure how to tell CountIf if it finds 1 by itself THEN count. If not then don't count. SumIf gives me the grievance of adding the numbers together that are all under 10, 20 or 30. I don't ...

This should be easy enough but my brain is fried and I can use the help. How can I formulate: Sum everything in column B when "Yes" is shown, as long as column A has the name "TONY" in it. I hope this makes sense. check out the detailed discussion at http://www.xldynamic.com/source/xld.SUMPRODUCT.html "tamato43" wrote: > This should be easy enough but my brain is fried and I can use the help. > > How can I formulate: > > Sum everything in column B when "Yes" is shown, as long as column A has the > name "TONY" in it...

I have a column populated with dates. I want a cell in another worksheet to count how many of the dates in the aforementioned column are less than a user defined date. I.e. I want the cell to display how many dates fall before 15th August. I am unsure as to how to write this correctly. Currently I have: =COUNTIF('Sheet1'!AH16:AH97, "<15/08/2005") Is this correct? What would SUMIF do in contrast? =COUNTIF('Sheet1'!AH16:AH97, "<"&Date(2005,08,15)) This just counts the instances. SUMIF can add up associated values in another column, such as AI...

Dear All, I have this set of data Name Value Third Column FourthColumn Count Names Total Value A 15 1 15 B 30 B 40 2 70 C 5 C 10 2 15 D 15 D 30 D 5 E 15 E 30 I want in a separate Third Column a) Count the number of Name...

Right now, I'm using the following function to get the average of column B, given that column A has a certain criteria: =SUMIF($A$1:$A$10,"=Blue",$B$1:$B$10)/COUNTIF($A$1:$A$10,"=Blue") Which works fine, except that for some of the entries whose A column says "blue" there is no data in the B column, so the denominator is off. How can I account for missing data in this function? Thanks in advance. Try =SUMIF($A$1:$A$10,"Blue",$B$1:$B$10)/SUMPRODUCT(($A$1:$A$10="Blue") *(B1:B10<>"")) OR try the below...

I'd like to look at a range of columns on each row and count the numbe of occurances of "R", "D", etc. I've tried Countif and Sumif and get zero back. I've tried looking for singles ("D") and still get zero. Any ideas? Thanks, Doug What did your formulas look like? Something like this: =countif(2:2,"D") Try this instead: =countif(2:2,"*D*") If you get a value other than 0, then I bet you have other stuff in those cells--not just a single character. In "*D*", the asterisks are wildcards and represent any number o...