Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Another Countif Formula
Hi. I want to find the amount of numbers in a given set that are greater than
a specific cell. The hard part is, the range depends on a list of dates in another column. I have from a1:a200 a descending list of dates, starting from today. I want a function that has the range start today (that's easy) and ends when the date hits March 1st (for example). Then the formula would apply that range to the column with the numbers. The reason the dates are not fixed is because the data is automatically updated from the internet so March 1st (for example) is always one cell lower. Example: Column A - 4/22/2005, 4/21/2005, etc. Column B - 5, 7, 6, 8, etc. Column C - The Number. I want to find out how many numbers in Column B are greater than the cell in Column C. The range depends on where March 1st (for example) ends in Column A. Can this even be done? Thanks so much for the help! -- Thanks! Stephen |
#2
|
|||
|
|||
Hi
I think you'll need to use SUMPRODUCT() for this. With your March 1st (for example) in D1, try: =SUMPRODUCT((A2:A1000<TODAY())*(A2:A1000D1)*(B2:B 1000C2)) When using SUMPRODUCT, you cannot use full column references and all references must be the same size. Hope this helps. -- Andy. "Stephen" wrote in message ... Hi. I want to find the amount of numbers in a given set that are greater than a specific cell. The hard part is, the range depends on a list of dates in another column. I have from a1:a200 a descending list of dates, starting from today. I want a function that has the range start today (that's easy) and ends when the date hits March 1st (for example). Then the formula would apply that range to the column with the numbers. The reason the dates are not fixed is because the data is automatically updated from the internet so March 1st (for example) is always one cell lower. Example: Column A - 4/22/2005, 4/21/2005, etc. Column B - 5, 7, 6, 8, etc. Column C - The Number. I want to find out how many numbers in Column B are greater than the cell in Column C. The range depends on where March 1st (for example) ends in Column A. Can this even be done? Thanks so much for the help! -- Thanks! Stephen |
#3
|
|||
|
|||
Andy, that worked great, thanks!
Could you also tell me the formula when the dates are not a concern. I want to find the number of cells greater than a1 in the set from a2:a12. Thanks! "Andy B" wrote: Hi I think you'll need to use SUMPRODUCT() for this. With your March 1st (for example) in D1, try: =SUMPRODUCT((A2:A1000<TODAY())*(A2:A1000D1)*(B2:B 1000C2)) When using SUMPRODUCT, you cannot use full column references and all references must be the same size. Hope this helps. -- Andy. "Stephen" wrote in message ... Hi. I want to find the amount of numbers in a given set that are greater than a specific cell. The hard part is, the range depends on a list of dates in another column. I have from a1:a200 a descending list of dates, starting from today. I want a function that has the range start today (that's easy) and ends when the date hits March 1st (for example). Then the formula would apply that range to the column with the numbers. The reason the dates are not fixed is because the data is automatically updated from the internet so March 1st (for example) is always one cell lower. Example: Column A - 4/22/2005, 4/21/2005, etc. Column B - 5, 7, 6, 8, etc. Column C - The Number. I want to find out how many numbers in Column B are greater than the cell in Column C. The range depends on where March 1st (for example) ends in Column A. Can this even be done? Thanks so much for the help! -- Thanks! Stephen |
#4
|
|||
|
|||
=COUNTIF(A2:A12,""&A1)
-- HTH RP (remove nothere from the email address if mailing direct) "Stephen" wrote in message ... Andy, that worked great, thanks! Could you also tell me the formula when the dates are not a concern. I want to find the number of cells greater than a1 in the set from a2:a12. Thanks! "Andy B" wrote: Hi I think you'll need to use SUMPRODUCT() for this. With your March 1st (for example) in D1, try: =SUMPRODUCT((A2:A1000<TODAY())*(A2:A1000D1)*(B2:B 1000C2)) When using SUMPRODUCT, you cannot use full column references and all references must be the same size. Hope this helps. -- Andy. "Stephen" wrote in message ... Hi. I want to find the amount of numbers in a given set that are greater than a specific cell. The hard part is, the range depends on a list of dates in another column. I have from a1:a200 a descending list of dates, starting from today. I want a function that has the range start today (that's easy) and ends when the date hits March 1st (for example). Then the formula would apply that range to the column with the numbers. The reason the dates are not fixed is because the data is automatically updated from the internet so March 1st (for example) is always one cell lower. Example: Column A - 4/22/2005, 4/21/2005, etc. Column B - 5, 7, 6, 8, etc. Column C - The Number. I want to find out how many numbers in Column B are greater than the cell in Column C. The range depends on where March 1st (for example) ends in Column A. Can this even be done? Thanks so much for the help! -- Thanks! Stephen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named SUM Formula with relative refernce(s) | Excel Worksheet Functions | |||
Frequency formula | Excel Worksheet Functions | |||
Multiple Condition Sumif Formula | Excel Worksheet Functions | |||
IF & VLOOKUP FORMULA | Excel Worksheet Functions | |||
Relative Indirect Formula Referencing? | Excel Worksheet Functions |