 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / October 2006excel 2000 - array formulas
Using Excel 2000 - I have 2700+ rows of data to analyze, spreadsheet has 15 columns of data. I want to count the rows which meet particular criteria for the "year" column (number formatted cells) and and the "Semi" column, also a number formatted cells. I just want a count of the # of rows in which say, year = 2005, semi = 1. I have not been able to get a "IF" formula to work nor an array formula. Any ideas? The countif function works when I only seek one set of data, like "2005", but selecting cell ranges and nesting functions is not working. Any Ideas? Try Sumproduct: =sumproduct((A1:A2700=2005)*(b1:b2700=1)) Where: Column A is your Year column and Column B is your Semi column. HTH. > Using Excel 2000 - I have 2700+ rows of data to analyze, spreadsheet has 15 > columns of data. I want to count the rows which meet particular criteria for [quoted text clipped - 4 lines] > seek one set of data, like "2005", but selecting cell ranges and nesting > functions is not working. Any Ideas? Thanks. It works but I have another question. Is there a way for me to refer to a cell that has 2005 in it so that I don't have to manually change this formula when I want to look at data for 2004? > Try Sumproduct: > [quoted text clipped - 15 lines] > > seek one set of data, like "2005", but selecting cell ranges and nesting > > functions is not working. Any Ideas? Sure thing. Just replace 2005 with the cell reference and you should be good to go. > Thanks. It works but I have another question. Is there a way for me to > refer to a cell that has 2005 in it so that I don't have to manually change [quoted text clipped - 19 lines] > > > seek one set of data, like "2005", but selecting cell ranges and nesting > > > functions is not working. Any Ideas? Thats where things are getting messed up. When I replace 2005 with the cell reference D2758, and the 1 with the cell reference E2758, the formula result is 0. Do I need to format the cell that has the 2005, and the 1 in them, differently? Do I need to put the cell references in as =value or =T nested cells? > Sure thing. Just replace 2005 with the cell reference and you should > be good to go. [quoted text clipped - 22 lines] > > > > seek one set of data, like "2005", but selecting cell ranges and nesting > > > > functions is not working. Any Ideas? Should work... can you post your formula? > Thats where things are getting messed up. When I replace 2005 with the cell > reference D2758, and the 1 with the cell reference E2758, the formula result [quoted text clipped - 28 lines] > > > > > seek one set of data, like "2005", but selecting cell ranges and nesting > > > > > functions is not working. Any Ideas? =SUMPRODUCT((D2:D2754=D2758)*(E2:E2754=E2758)) Year Semi Qtr. Month Recorded 2005 1 3 August 2005 307 0 81 29 The following works for quarter 3. =SUMPRODUCT(((D2:D2754=2005)*(E2:E2754=1)*(F2:F2754=3))) I'm hoping to change the year, semi, and quarter and watch formula results change without changing the formula. Thanks for your help. > Should work... can you post your formula? > [quoted text clipped - 30 lines] > > > > > > seek one set of data, like "2005", but selecting cell ranges and nesting > > > > > > functions is not working. Any Ideas? $D$2758 $E$2758 ? > =SUMPRODUCT((D2:D2754=D2758)*(E2:E2754=E2758)) > [quoted text clipped - 57 lines] >> > > > > > and nesting >> > > > > > functions is not working. Any Ideas? I tried. Just isn't working. I've tried reformatting the data to general from numerical and vice versa but still no success. Thanks for the suggestion. It lets me know I'm on the right track. Daniel > $D$2758 $E$2758 ? > [quoted text clipped - 59 lines] > >> > > > > > and nesting > >> > > > > > functions is not working. Any Ideas? Hi If cells D2758, E2758, F2758 hold the numeric values 2005, 1 , 3 respectively, then you should see exactly the same result. Varying the values in those cells, will then alter the results shown. If you are copying the cells with the formulae to other cells, you should make the ranges and the comparative cells absolute. =SUMPRODUCT(($D$2:$D$2754=$D2758)* ($E2$:$E$2754=$E$2758)*($F$2:$F$2754=$F$2758))  SignatureRegards Roger Govier > =SUMPRODUCT((D2:D2754=D2758)*(E2:E2754=E2758)) > [quoted text clipped - 59 lines] >> > > > > > ranges and nesting >> > > > > > functions is not working. Any Ideas? Thanks. Unfortunately I've tried this and it still isn't working. I've tried making the values absolute and it just won't take the cell reference but will take the numerical value in the formula. Thanks again. Daniel > Hi > [quoted text clipped - 71 lines] > >> > > > > > ranges and nesting > >> > > > > > functions is not working. Any Ideas? Check out Chip Pearson's website. The URL below brings you to a page that shows you how to use multiple criteria for both SumIf and CountIf. http://www.cpearson.com/excel/array.htm HTH, SignatureGary Brown gary_brown@ge_NOSPAM.com If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. > Using Excel 2000 - I have 2700+ rows of data to analyze, spreadsheet has 15 > columns of data. I want to count the rows which meet particular criteria for [quoted text clipped - 4 lines] > seek one set of data, like "2005", but selecting cell ranges and nesting > functions is not working. Any Ideas? Thanks. I'll check this out. > Check out Chip Pearson's website. > The URL below brings you to a page that shows you how to use multiple [quoted text clipped - 12 lines] > > seek one set of data, like "2005", but selecting cell ranges and nesting > > functions is not working. Any Ideas? =SUMPRODUCT(--(A2:A2700=2005),--(C2:C2700=1)) SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > Using Excel 2000 - I have 2700+ rows of data to analyze, spreadsheet has 15 > columns of data. I want to count the rows which meet particular criteria for [quoted text clipped - 4 lines] > seek one set of data, like "2005", but selecting cell ranges and nesting > functions is not working. Any Ideas? Bob, If you look at the other responses I made to other posts about this issue, you can see I'm pretty beat. But your solution WORKS!!! I don't know why the -- characters make it work with absolute cell references but it does!!! In adjoining cells I have =COUNTIF($D$2:$D$2754,$D$2758) working for the year and it works fine for changing the year. So, since you seem to have the answer I've spent 2 days on, will this also work for other data in the spreadsheet even if the criteria is not a number? Also, can you tell me or point me to a resource that explains the -- characters in the formula? Thanks Daniel > =SUMPRODUCT(--(A2:A2700=2005),--(C2:C2700=1)) > [quoted text clipped - 10 lines] > > seek one set of data, like "2005", but selecting cell ranges and nesting > > functions is not working. Any Ideas? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.