Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 2000 - array formulas
Try Sumproduct:
=sumproduct((A1:A2700=2005)*(b1:b2700=1)) Whe Column A is your Year column and Column B is your Semi column. HTH. cdsta wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 2000 - array formulas
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, -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "cdsta" wrote: 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 2000 - array formulas
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? "willwonka" wrote: Try Sumproduct: =sumproduct((A1:A2700=2005)*(b1:b2700=1)) Whe Column A is your Year column and Column B is your Semi column. HTH. cdsta wrote: 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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 2000 - array formulas
Sure thing. Just replace 2005 with the cell reference and you should
be good to go. cdsta wrote: 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? "willwonka" wrote: Try Sumproduct: =sumproduct((A1:A2700=2005)*(b1:b2700=1)) Whe Column A is your Year column and Column B is your Semi column. HTH. cdsta wrote: 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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 2000 - array formulas
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? "willwonka" wrote: Sure thing. Just replace 2005 with the cell reference and you should be good to go. cdsta wrote: 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? "willwonka" wrote: Try Sumproduct: =sumproduct((A1:A2700=2005)*(b1:b2700=1)) Whe Column A is your Year column and Column B is your Semi column. HTH. cdsta wrote: 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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 2000 - array formulas
Should work... can you post your formula?
cdsta wrote: 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? "willwonka" wrote: Sure thing. Just replace 2005 with the cell reference and you should be good to go. cdsta wrote: 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? "willwonka" wrote: Try Sumproduct: =sumproduct((A1:A2700=2005)*(b1:b2700=1)) Whe Column A is your Year column and Column B is your Semi column. HTH. cdsta wrote: 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? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 2000 - array formulas
=SUMPRODUCT(--(A2:A2700=2005),--(C2:C2700=1))
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "cdsta" wrote in message ... 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? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 2000 - array formulas
=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:F275 4=3))) I'm hoping to change the year, semi, and quarter and watch formula results change without changing the formula. Thanks for your help. "willwonka" wrote: Should work... can you post your formula? cdsta wrote: 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? "willwonka" wrote: Sure thing. Just replace 2005 with the cell reference and you should be good to go. cdsta wrote: 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? "willwonka" wrote: Try Sumproduct: =sumproduct((A1:A2700=2005)*(b1:b2700=1)) Whe Column A is your Year column and Column B is your Semi column. HTH. cdsta wrote: 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? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 2000 - array formulas
$D$2758 $E$2758 ?
"cdsta" wrote in message ... =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:F275 4=3))) I'm hoping to change the year, semi, and quarter and watch formula results change without changing the formula. Thanks for your help. "willwonka" wrote: Should work... can you post your formula? cdsta wrote: 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? "willwonka" wrote: Sure thing. Just replace 2005 with the cell reference and you should be good to go. cdsta wrote: 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? "willwonka" wrote: Try Sumproduct: =sumproduct((A1:A2700=2005)*(b1:b2700=1)) Whe Column A is your Year column and Column B is your Semi column. HTH. cdsta wrote: 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? |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 2000 - array formulas
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)) -- Regards Roger Govier "cdsta" wrote in message ... =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:F275 4=3))) I'm hoping to change the year, semi, and quarter and watch formula results change without changing the formula. Thanks for your help. "willwonka" wrote: Should work... can you post your formula? cdsta wrote: 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? "willwonka" wrote: Sure thing. Just replace 2005 with the cell reference and you should be good to go. cdsta wrote: 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? "willwonka" wrote: Try Sumproduct: =sumproduct((A1:A2700=2005)*(b1:b2700=1)) Whe Column A is your Year column and Column B is your Semi column. HTH. cdsta wrote: 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? |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 2000 - array formulas
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 "Roger Govier" wrote: 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)) -- Regards Roger Govier "cdsta" wrote in message ... =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:F275 4=3))) I'm hoping to change the year, semi, and quarter and watch formula results change without changing the formula. Thanks for your help. "willwonka" wrote: Should work... can you post your formula? cdsta wrote: 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? "willwonka" wrote: Sure thing. Just replace 2005 with the cell reference and you should be good to go. cdsta wrote: 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? "willwonka" wrote: Try Sumproduct: =sumproduct((A1:A2700=2005)*(b1:b2700=1)) Whe Column A is your Year column and Column B is your Semi column. HTH. cdsta wrote: 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? |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 2000 - array formulas
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 "David F Cox" wrote: $D$2758 $E$2758 ? "cdsta" wrote in message ... =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:F275 4=3))) I'm hoping to change the year, semi, and quarter and watch formula results change without changing the formula. Thanks for your help. "willwonka" wrote: Should work... can you post your formula? cdsta wrote: 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? "willwonka" wrote: Sure thing. Just replace 2005 with the cell reference and you should be good to go. cdsta wrote: 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? "willwonka" wrote: Try Sumproduct: =sumproduct((A1:A2700=2005)*(b1:b2700=1)) Whe Column A is your Year column and Column B is your Semi column. HTH. cdsta wrote: 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? |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 2000 - array formulas
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 "Bob Phillips" wrote: =SUMPRODUCT(--(A2:A2700=2005),--(C2:C2700=1)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "cdsta" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I run excel 4.0 macros on excel 2000 | Excel Discussion (Misc queries) | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
Data from Excel 2000 Worksheet with external links is not displayed when opened in Excel 2003 | Links and Linking in Excel | |||
Excel 2003 crashes loading excel files created Excel 2000 | Excel Discussion (Misc queries) | |||
Microsoft Excel 2003 and Hyperion Retrieve with Excel 2000. | Excel Discussion (Misc queries) |