Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create filter using criteria from other cells.
I have a spreadsheet with golf tournament data. I have an analysis sheet
showing the counts of various scores on each hole. (10 Birdies, 5 Aces, etc). Now I want to use criteria fields (cells) to filter the data based on another column. For instance, I want to see the data analysis on players with a rating between 950 and 1000. I must have cells for the user to input the criteria he/she wants and not actually filter the data, since more than one analysis can be made at the same time. (multiple analysis) I already have a formula for counting the various scores. I have used COUNTIF for the analysis. I tried using COUNTIFS, but the criteria seems to be only on the column of data that is to be counted. I need to filter using another column as well. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create filter using criteria from other cells.
Use Advanced Filter
For details look for "Filter by using advanced criteria " in Excel Help "JLewis" wrote: I have a spreadsheet with golf tournament data. I have an analysis sheet showing the counts of various scores on each hole. (10 Birdies, 5 Aces, etc). Now I want to use criteria fields (cells) to filter the data based on another column. For instance, I want to see the data analysis on players with a rating between 950 and 1000. I must have cells for the user to input the criteria he/she wants and not actually filter the data, since more than one analysis can be made at the same time. (multiple analysis) I already have a formula for counting the various scores. I have used COUNTIF for the analysis. I tried using COUNTIFS, but the criteria seems to be only on the column of data that is to be counted. I need to filter using another column as well. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create filter using criteria from other cells.
Hi,
Since you are using 2007, COUNTIFS does support multiple criteria columns (ranges). Suppose you want to count the number of players with ratings between 950 and 1000. Suppose the rating is in column D1:D100 =COUNTIFS(D1:D100,"=950",D1:D100,"<=1000") would do it. You also asked about the fact that for countifs "the criteria seems to be only on the column of data that is to be counted" I need an example of this to understand the problem, but lets suppose you want to count the number of golfers with ratings above 1000 who are female. The the data on gender is in column C, using the ranges mentioned in the first example, then =COUNTIFS(D1:D100,"1000",C1:C100,"Female") Since you said you don't want to actually filter the data, if you are using Advanced Filter you must choose copy to a new location, otherwise you will be filtering the data. FYI - COUNTIFS and SUMIFS and AVERAGEIFS and their single criteria brothers are powerful but limited, to get around these limit consider the D-FUNCTIONS. These functions are really unlimited in their power. Their basic syntax is =DSUM(Database,Column,Criteria) where database is your data with one row of titles, column is the column you want to SUM, and criteria is a spreadsheet range where your user can enter as complicated a criteria as they want. It takes some work learning these but its worth it. Besides DSUM there is DCOUNT, DMAX, DMIN, ... If this helps please click the Yes button. -- Thanks, Shane Devenshire "JLewis" wrote: I have a spreadsheet with golf tournament data. I have an analysis sheet showing the counts of various scores on each hole. (10 Birdies, 5 Aces, etc). Now I want to use criteria fields (cells) to filter the data based on another column. For instance, I want to see the data analysis on players with a rating between 950 and 1000. I must have cells for the user to input the criteria he/she wants and not actually filter the data, since more than one analysis can be made at the same time. (multiple analysis) I already have a formula for counting the various scores. I have used COUNTIF for the analysis. I tried using COUNTIFS, but the criteria seems to be only on the column of data that is to be counted. I need to filter using another column as well. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create filter using criteria from other cells.
Thanks for the info. I have tried using COUNTIFS, but I ran into trouble
trying to reference a cell for the criteria. I found a post that mentioned using cell references for SUMIF and COUNTIF. The example was for SUMIF, but I think I understand the bulk of the idea. So if, I used: =COUNTIFS(F3:F203, ""&A1,F3:F203,"<"&A2) would this work? I will try it later when I get back to my project. Thanks again, Jennifer "ShaneDevenshire" wrote: Hi, Since you are using 2007, COUNTIFS does support multiple criteria columns (ranges). Suppose you want to count the number of players with ratings between 950 and 1000. Suppose the rating is in column D1:D100 =COUNTIFS(D1:D100,"=950",D1:D100,"<=1000") would do it. You also asked about the fact that for countifs "the criteria seems to be only on the column of data that is to be counted" I need an example of this to understand the problem, but lets suppose you want to count the number of golfers with ratings above 1000 who are female. The the data on gender is in column C, using the ranges mentioned in the first example, then =COUNTIFS(D1:D100,"1000",C1:C100,"Female") Since you said you don't want to actually filter the data, if you are using Advanced Filter you must choose copy to a new location, otherwise you will be filtering the data. FYI - COUNTIFS and SUMIFS and AVERAGEIFS and their single criteria brothers are powerful but limited, to get around these limit consider the D-FUNCTIONS. These functions are really unlimited in their power. Their basic syntax is =DSUM(Database,Column,Criteria) where database is your data with one row of titles, column is the column you want to SUM, and criteria is a spreadsheet range where your user can enter as complicated a criteria as they want. It takes some work learning these but its worth it. Besides DSUM there is DCOUNT, DMAX, DMIN, ... If this helps please click the Yes button. -- Thanks, Shane Devenshire "JLewis" wrote: I have a spreadsheet with golf tournament data. I have an analysis sheet showing the counts of various scores on each hole. (10 Birdies, 5 Aces, etc). Now I want to use criteria fields (cells) to filter the data based on another column. For instance, I want to see the data analysis on players with a rating between 950 and 1000. I must have cells for the user to input the criteria he/she wants and not actually filter the data, since more than one analysis can be made at the same time. (multiple analysis) I already have a formula for counting the various scores. I have used COUNTIF for the analysis. I tried using COUNTIFS, but the criteria seems to be only on the column of data that is to be counted. I need to filter using another column as well. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create filter using criteria from other cells.
Yes it would, however I would rather
use this instead =SUMPRODUCT(--(F3:F203A1),--(F3:F203<A2)) since it is compatible with earlier versions -- Regards, Peo Sjoblom "JLewis" wrote in message ... Thanks for the info. I have tried using COUNTIFS, but I ran into trouble trying to reference a cell for the criteria. I found a post that mentioned using cell references for SUMIF and COUNTIF. The example was for SUMIF, but I think I understand the bulk of the idea. So if, I used: =COUNTIFS(F3:F203, ""&A1,F3:F203,"<"&A2) would this work? I will try it later when I get back to my project. Thanks again, Jennifer "ShaneDevenshire" wrote: Hi, Since you are using 2007, COUNTIFS does support multiple criteria columns (ranges). Suppose you want to count the number of players with ratings between 950 and 1000. Suppose the rating is in column D1:D100 =COUNTIFS(D1:D100,"=950",D1:D100,"<=1000") would do it. You also asked about the fact that for countifs "the criteria seems to be only on the column of data that is to be counted" I need an example of this to understand the problem, but lets suppose you want to count the number of golfers with ratings above 1000 who are female. The the data on gender is in column C, using the ranges mentioned in the first example, then =COUNTIFS(D1:D100,"1000",C1:C100,"Female") Since you said you don't want to actually filter the data, if you are using Advanced Filter you must choose copy to a new location, otherwise you will be filtering the data. FYI - COUNTIFS and SUMIFS and AVERAGEIFS and their single criteria brothers are powerful but limited, to get around these limit consider the D-FUNCTIONS. These functions are really unlimited in their power. Their basic syntax is =DSUM(Database,Column,Criteria) where database is your data with one row of titles, column is the column you want to SUM, and criteria is a spreadsheet range where your user can enter as complicated a criteria as they want. It takes some work learning these but its worth it. Besides DSUM there is DCOUNT, DMAX, DMIN, ... If this helps please click the Yes button. -- Thanks, Shane Devenshire "JLewis" wrote: I have a spreadsheet with golf tournament data. I have an analysis sheet showing the counts of various scores on each hole. (10 Birdies, 5 Aces, etc). Now I want to use criteria fields (cells) to filter the data based on another column. For instance, I want to see the data analysis on players with a rating between 950 and 1000. I must have cells for the user to input the criteria he/she wants and not actually filter the data, since more than one analysis can be made at the same time. (multiple analysis) I already have a formula for counting the various scores. I have used COUNTIF for the analysis. I tried using COUNTIFS, but the criteria seems to be only on the column of data that is to be counted. I need to filter using another column as well. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create filter using criteria from other cells.
Okay, this works great for criteria that references the data I'm counting.
What about criteria based on another column? I have columns for division, rating, name, and for holes 1-18. I also have the first row that gives the hole pars. I have a formula for reporting birdies, bogeys, etc. But I'd like to more criteria to filter the data. Here is what I have in mind, but it will not work. =COUNTIFS(D3:D808,D2-1,A3:A808,"="&A1,B3:B808,""&A2,B3:B808,"<"&BA3 "D2-1" references the par for the hole less one. Essentially a birdie. A1,A2,A3 cells are for defining the filter criteria. This should give me all of the birdies for the players' scores that fit the criteria, shouldn't it? I like how you used the formula for being able to use an earlier form of Excel. I don't know that it would be necessary, but it is still nice to know that I could. "Peo Sjoblom" wrote: Yes it would, however I would rather use this instead =SUMPRODUCT(--(F3:F203A1),--(F3:F203<A2)) since it is compatible with earlier versions -- Regards, Peo Sjoblom |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create filter using criteria from other cells.
Try
=SUMPRODUCT(--(D3:D808=D2-1),--(A3:A808=A1),--(B3:B808A2),--(B3:B808<BA3)) -- Regards, Peo Sjoblom "JLewis" wrote in message ... Okay, this works great for criteria that references the data I'm counting. What about criteria based on another column? I have columns for division, rating, name, and for holes 1-18. I also have the first row that gives the hole pars. I have a formula for reporting birdies, bogeys, etc. But I'd like to more criteria to filter the data. Here is what I have in mind, but it will not work. =COUNTIFS(D3:D808,D2-1,A3:A808,"="&A1,B3:B808,""&A2,B3:B808,"<"&BA3 "D2-1" references the par for the hole less one. Essentially a birdie. A1,A2,A3 cells are for defining the filter criteria. This should give me all of the birdies for the players' scores that fit the criteria, shouldn't it? I like how you used the formula for being able to use an earlier form of Excel. I don't know that it would be necessary, but it is still nice to know that I could. "Peo Sjoblom" wrote: Yes it would, however I would rather use this instead =SUMPRODUCT(--(F3:F203A1),--(F3:F203<A2)) since it is compatible with earlier versions -- Regards, Peo Sjoblom |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create filter using criteria from other cells.
It only gave me a "0". When I know for the criteria there should be 282
Birdies. I really appreciate the help! "Peo Sjoblom" wrote: Try =SUMPRODUCT(--(D3:D808=D2-1),--(A3:A808=A1),--(B3:B808A2),--(B3:B808<BA3)) -- Regards, Peo Sjoblom "JLewis" wrote in message ... Okay, this works great for criteria that references the data I'm counting. What about criteria based on another column? I have columns for division, rating, name, and for holes 1-18. I also have the first row that gives the hole pars. I have a formula for reporting birdies, bogeys, etc. But I'd like to more criteria to filter the data. Here is what I have in mind, but it will not work. =COUNTIFS(D3:D808,D2-1,A3:A808,"="&A1,B3:B808,""&A2,B3:B808,"<"&BA3 "D2-1" references the par for the hole less one. Essentially a birdie. A1,A2,A3 cells are for defining the filter criteria. This should give me all of the birdies for the players' scores that fit the criteria, shouldn't it? I like how you used the formula for being able to use an earlier form of Excel. I don't know that it would be necessary, but it is still nice to know that I could. "Peo Sjoblom" wrote: Yes it would, however I would rather use this instead =SUMPRODUCT(--(F3:F203A1),--(F3:F203<A2)) since it is compatible with earlier versions -- Regards, Peo Sjoblom |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create filter using criteria from other cells.
I noticed you wrote A1, A2 and A3 whereas your formula showed BA3
If you really meant A3 instead and if you pasted in the formula I gave you that might explain it. If that's the case change BA3 to A3 at the end The formula I gave you will count where D3:D808 equals D2-1 AND A3:A808 equals A1 AND where B3:B808 is greater than A2 AND less than either BA3 or A3 whichever it is supposed to be. -- Regards, Peo Sjoblom "JLewis" wrote in message ... It only gave me a "0". When I know for the criteria there should be 282 Birdies. I really appreciate the help! "Peo Sjoblom" wrote: Try =SUMPRODUCT(--(D3:D808=D2-1),--(A3:A808=A1),--(B3:B808A2),--(B3:B808<BA3)) -- Regards, Peo Sjoblom "JLewis" wrote in message ... Okay, this works great for criteria that references the data I'm counting. What about criteria based on another column? I have columns for division, rating, name, and for holes 1-18. I also have the first row that gives the hole pars. I have a formula for reporting birdies, bogeys, etc. But I'd like to more criteria to filter the data. Here is what I have in mind, but it will not work. =COUNTIFS(D3:D808,D2-1,A3:A808,"="&A1,B3:B808,""&A2,B3:B808,"<"&BA3 "D2-1" references the par for the hole less one. Essentially a birdie. A1,A2,A3 cells are for defining the filter criteria. This should give me all of the birdies for the players' scores that fit the criteria, shouldn't it? I like how you used the formula for being able to use an earlier form of Excel. I don't know that it would be necessary, but it is still nice to know that I could. "Peo Sjoblom" wrote: Yes it would, however I would rather use this instead =SUMPRODUCT(--(F3:F203A1),--(F3:F203<A2)) since it is compatible with earlier versions -- Regards, Peo Sjoblom |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create filter using criteria from other cells.
Yes, I did catch my typo. I was wondering in the formula, where does it state
what to count? I ran a formula analysis and I see the true/false of the criteria search, but it doesn't seem to be counting anything in the D column where it should. "Peo Sjoblom" wrote: I noticed you wrote A1, A2 and A3 whereas your formula showed BA3 If you really meant A3 instead and if you pasted in the formula I gave you that might explain it. If that's the case change BA3 to A3 at the end The formula I gave you will count where D3:D808 equals D2-1 AND A3:A808 equals A1 AND where B3:B808 is greater than A2 AND less than either BA3 or A3 whichever it is supposed to be. -- Regards, Peo Sjoblom "JLewis" wrote in message ... It only gave me a "0". When I know for the criteria there should be 282 Birdies. I really appreciate the help! "Peo Sjoblom" wrote: Try =SUMPRODUCT(--(D3:D808=D2-1),--(A3:A808=A1),--(B3:B808A2),--(B3:B808<BA3)) -- Regards, Peo Sjoblom "JLewis" wrote in message ... Okay, this works great for criteria that references the data I'm counting. What about criteria based on another column? I have columns for division, rating, name, and for holes 1-18. I also have the first row that gives the hole pars. I have a formula for reporting birdies, bogeys, etc. But I'd like to more criteria to filter the data. Here is what I have in mind, but it will not work. =COUNTIFS(D3:D808,D2-1,A3:A808,"="&A1,B3:B808,""&A2,B3:B808,"<"&BA3 "D2-1" references the par for the hole less one. Essentially a birdie. A1,A2,A3 cells are for defining the filter criteria. This should give me all of the birdies for the players' scores that fit the criteria, shouldn't it? I like how you used the formula for being able to use an earlier form of Excel. I don't know that it would be necessary, but it is still nice to know that I could. "Peo Sjoblom" wrote: Yes it would, however I would rather use this instead =SUMPRODUCT(--(F3:F203A1),--(F3:F203<A2)) since it is compatible with earlier versions -- Regards, Peo Sjoblom |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create filter using criteria from other cells.
Okay, I have finally gotten it to work. I had the criteria cells mixed up. I
entered criteria for rating in the division cell. Yeah! It works! You are a genius. Thank you very much ! "JLewis" wrote: Yes, I did catch my typo. I was wondering in the formula, where does it state what to count? I ran a formula analysis and I see the true/false of the criteria search, but it doesn't seem to be counting anything in the D column where it should. "Peo Sjoblom" wrote: I noticed you wrote A1, A2 and A3 whereas your formula showed BA3 If you really meant A3 instead and if you pasted in the formula I gave you that might explain it. If that's the case change BA3 to A3 at the end The formula I gave you will count where D3:D808 equals D2-1 AND A3:A808 equals A1 AND where B3:B808 is greater than A2 AND less than either BA3 or A3 whichever it is supposed to be. -- Regards, Peo Sjoblom "JLewis" wrote in message ... It only gave me a "0". When I know for the criteria there should be 282 Birdies. I really appreciate the help! "Peo Sjoblom" wrote: Try =SUMPRODUCT(--(D3:D808=D2-1),--(A3:A808=A1),--(B3:B808A2),--(B3:B808<BA3)) -- Regards, Peo Sjoblom "JLewis" wrote in message ... Okay, this works great for criteria that references the data I'm counting. What about criteria based on another column? I have columns for division, rating, name, and for holes 1-18. I also have the first row that gives the hole pars. I have a formula for reporting birdies, bogeys, etc. But I'd like to more criteria to filter the data. Here is what I have in mind, but it will not work. =COUNTIFS(D3:D808,D2-1,A3:A808,"="&A1,B3:B808,""&A2,B3:B808,"<"&BA3 "D2-1" references the par for the hole less one. Essentially a birdie. A1,A2,A3 cells are for defining the filter criteria. This should give me all of the birdies for the players' scores that fit the criteria, shouldn't it? I like how you used the formula for being able to use an earlier form of Excel. I don't know that it would be necessary, but it is still nice to know that I could. "Peo Sjoblom" wrote: Yes it would, however I would rather use this instead =SUMPRODUCT(--(F3:F203A1),--(F3:F203<A2)) since it is compatible with earlier versions -- Regards, Peo Sjoblom |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create filter using criteria from other cells.
It counts how many values in D3:D808 are equal to D2-1.
Note that if SUMIFS and COUNTIFS are the same as COUNTIF and SUMIF than they disregard what is known as text number. For instance =COUNTIF(A1:A10,1) returns the same value as =COUNTIF(A1:A10,"1") but =SUMPRODUCT(--(A1:A10=1)) will not return the same as =SUMPRODUCT(--(A1:A10="1")) so if there is a number that looks like a number but is seen by Excel as text COUNTIF will not make a difference but SUMPRODUCT will and I never use the new functions in 2007 but if they are similar that might explain it -- Regards, Peo Sjoblom "JLewis" wrote in message ... Yes, I did catch my typo. I was wondering in the formula, where does it state what to count? I ran a formula analysis and I see the true/false of the criteria search, but it doesn't seem to be counting anything in the D column where it should. "Peo Sjoblom" wrote: I noticed you wrote A1, A2 and A3 whereas your formula showed BA3 If you really meant A3 instead and if you pasted in the formula I gave you that might explain it. If that's the case change BA3 to A3 at the end The formula I gave you will count where D3:D808 equals D2-1 AND A3:A808 equals A1 AND where B3:B808 is greater than A2 AND less than either BA3 or A3 whichever it is supposed to be. -- Regards, Peo Sjoblom "JLewis" wrote in message ... It only gave me a "0". When I know for the criteria there should be 282 Birdies. I really appreciate the help! "Peo Sjoblom" wrote: Try =SUMPRODUCT(--(D3:D808=D2-1),--(A3:A808=A1),--(B3:B808A2),--(B3:B808<BA3)) -- Regards, Peo Sjoblom "JLewis" wrote in message ... Okay, this works great for criteria that references the data I'm counting. What about criteria based on another column? I have columns for division, rating, name, and for holes 1-18. I also have the first row that gives the hole pars. I have a formula for reporting birdies, bogeys, etc. But I'd like to more criteria to filter the data. Here is what I have in mind, but it will not work. =COUNTIFS(D3:D808,D2-1,A3:A808,"="&A1,B3:B808,""&A2,B3:B808,"<"&BA3 "D2-1" references the par for the hole less one. Essentially a birdie. A1,A2,A3 cells are for defining the filter criteria. This should give me all of the birdies for the players' scores that fit the criteria, shouldn't it? I like how you used the formula for being able to use an earlier form of Excel. I don't know that it would be necessary, but it is still nice to know that I could. "Peo Sjoblom" wrote: Yes it would, however I would rather use this instead =SUMPRODUCT(--(F3:F203A1),--(F3:F203<A2)) since it is compatible with earlier versions -- Regards, Peo Sjoblom |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create filter using criteria from other cells.
One more thing...(I know, I'm needy!). If I wanted to see for instance, all
divisions, what would I put in the criteria cell? I know for the ratings, I could put 1 and 2000 or something like that, given ratings are between 1 and 1100. But divisions are different. Thanks again. "JLewis" wrote: Okay, I have finally gotten it to work. I had the criteria cells mixed up. I entered criteria for rating in the division cell. Yeah! It works! You are a genius. Thank you very much ! "JLewis" wrote: Yes, I did catch my typo. I was wondering in the formula, where does it state what to count? I ran a formula analysis and I see the true/false of the criteria search, but it doesn't seem to be counting anything in the D column where it should. "Peo Sjoblom" wrote: I noticed you wrote A1, A2 and A3 whereas your formula showed BA3 If you really meant A3 instead and if you pasted in the formula I gave you that might explain it. If that's the case change BA3 to A3 at the end The formula I gave you will count where D3:D808 equals D2-1 AND A3:A808 equals A1 AND where B3:B808 is greater than A2 AND less than either BA3 or A3 whichever it is supposed to be. -- Regards, Peo Sjoblom "JLewis" wrote in message ... It only gave me a "0". When I know for the criteria there should be 282 Birdies. I really appreciate the help! "Peo Sjoblom" wrote: Try =SUMPRODUCT(--(D3:D808=D2-1),--(A3:A808=A1),--(B3:B808A2),--(B3:B808<BA3)) -- Regards, Peo Sjoblom "JLewis" wrote in message ... Okay, this works great for criteria that references the data I'm counting. What about criteria based on another column? I have columns for division, rating, name, and for holes 1-18. I also have the first row that gives the hole pars. I have a formula for reporting birdies, bogeys, etc. But I'd like to more criteria to filter the data. Here is what I have in mind, but it will not work. =COUNTIFS(D3:D808,D2-1,A3:A808,"="&A1,B3:B808,""&A2,B3:B808,"<"&BA3 "D2-1" references the par for the hole less one. Essentially a birdie. A1,A2,A3 cells are for defining the filter criteria. This should give me all of the birdies for the players' scores that fit the criteria, shouldn't it? I like how you used the formula for being able to use an earlier form of Excel. I don't know that it would be necessary, but it is still nice to know that I could. "Peo Sjoblom" wrote: Yes it would, however I would rather use this instead =SUMPRODUCT(--(F3:F203A1),--(F3:F203<A2)) since it is compatible with earlier versions -- Regards, Peo Sjoblom |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create filter using criteria from other cells.
Thanks. I really don't know too much about how the functions work.I will be
taking a course soon. Your explanation very helpful. Jennifer "Peo Sjoblom" wrote: It counts how many values in D3:D808 are equal to D2-1. Note that if SUMIFS and COUNTIFS are the same as COUNTIF and SUMIF than they disregard what is known as text number. For instance =COUNTIF(A1:A10,1) returns the same value as =COUNTIF(A1:A10,"1") but =SUMPRODUCT(--(A1:A10=1)) will not return the same as =SUMPRODUCT(--(A1:A10="1")) so if there is a number that looks like a number but is seen by Excel as text COUNTIF will not make a difference but SUMPRODUCT will and I never use the new functions in 2007 but if they are similar that might explain it -- Regards, Peo Sjoblom "JLewis" wrote in message ... Yes, I did catch my typo. I was wondering in the formula, where does it state what to count? I ran a formula analysis and I see the true/false of the criteria search, but it doesn't seem to be counting anything in the D column where it should. "Peo Sjoblom" wrote: I noticed you wrote A1, A2 and A3 whereas your formula showed BA3 If you really meant A3 instead and if you pasted in the formula I gave you that might explain it. If that's the case change BA3 to A3 at the end The formula I gave you will count where D3:D808 equals D2-1 AND A3:A808 equals A1 AND where B3:B808 is greater than A2 AND less than either BA3 or A3 whichever it is supposed to be. -- Regards, Peo Sjoblom "JLewis" wrote in message ... It only gave me a "0". When I know for the criteria there should be 282 Birdies. I really appreciate the help! "Peo Sjoblom" wrote: Try =SUMPRODUCT(--(D3:D808=D2-1),--(A3:A808=A1),--(B3:B808A2),--(B3:B808<BA3)) -- Regards, Peo Sjoblom "JLewis" wrote in message ... Okay, this works great for criteria that references the data I'm counting. What about criteria based on another column? I have columns for division, rating, name, and for holes 1-18. I also have the first row that gives the hole pars. I have a formula for reporting birdies, bogeys, etc. But I'd like to more criteria to filter the data. Here is what I have in mind, but it will not work. =COUNTIFS(D3:D808,D2-1,A3:A808,"="&A1,B3:B808,""&A2,B3:B808,"<"&BA3 "D2-1" references the par for the hole less one. Essentially a birdie. A1,A2,A3 cells are for defining the filter criteria. This should give me all of the birdies for the players' scores that fit the criteria, shouldn't it? I like how you used the formula for being able to use an earlier form of Excel. I don't know that it would be necessary, but it is still nice to know that I could. "Peo Sjoblom" wrote: Yes it would, however I would rather use this instead =SUMPRODUCT(--(F3:F203A1),--(F3:F203<A2)) since it is compatible with earlier versions -- Regards, Peo Sjoblom |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create filter using criteria from other cells.
I am not sure I understand, which column is the division?
-- Regards, Peo Sjoblom "JLewis" wrote in message ... One more thing...(I know, I'm needy!). If I wanted to see for instance, all divisions, what would I put in the criteria cell? I know for the ratings, I could put 1 and 2000 or something like that, given ratings are between 1 and 1100. But divisions are different. Thanks again. "JLewis" wrote: Okay, I have finally gotten it to work. I had the criteria cells mixed up. I entered criteria for rating in the division cell. Yeah! It works! You are a genius. Thank you very much ! "JLewis" wrote: Yes, I did catch my typo. I was wondering in the formula, where does it state what to count? I ran a formula analysis and I see the true/false of the criteria search, but it doesn't seem to be counting anything in the D column where it should. "Peo Sjoblom" wrote: I noticed you wrote A1, A2 and A3 whereas your formula showed BA3 If you really meant A3 instead and if you pasted in the formula I gave you that might explain it. If that's the case change BA3 to A3 at the end The formula I gave you will count where D3:D808 equals D2-1 AND A3:A808 equals A1 AND where B3:B808 is greater than A2 AND less than either BA3 or A3 whichever it is supposed to be. -- Regards, Peo Sjoblom "JLewis" wrote in message ... It only gave me a "0". When I know for the criteria there should be 282 Birdies. I really appreciate the help! "Peo Sjoblom" wrote: Try =SUMPRODUCT(--(D3:D808=D2-1),--(A3:A808=A1),--(B3:B808A2),--(B3:B808<BA3)) -- Regards, Peo Sjoblom "JLewis" wrote in message ... Okay, this works great for criteria that references the data I'm counting. What about criteria based on another column? I have columns for division, rating, name, and for holes 1-18. I also have the first row that gives the hole pars. I have a formula for reporting birdies, bogeys, etc. But I'd like to more criteria to filter the data. Here is what I have in mind, but it will not work. =COUNTIFS(D3:D808,D2-1,A3:A808,"="&A1,B3:B808,""&A2,B3:B808,"<"&BA3 "D2-1" references the par for the hole less one. Essentially a birdie. A1,A2,A3 cells are for defining the filter criteria. This should give me all of the birdies for the players' scores that fit the criteria, shouldn't it? I like how you used the formula for being able to use an earlier form of Excel. I don't know that it would be necessary, but it is still nice to know that I could. "Peo Sjoblom" wrote: Yes it would, however I would rather use this instead =SUMPRODUCT(--(F3:F203A1),--(F3:F203<A2)) since it is compatible with earlier versions -- Regards, Peo Sjoblom |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create filter using criteria from other cells.
A:3:A808. This could be a text, like Pro Amateur, Junior, Women, etc. For my
purposes right now, I have it as a number, like 1, 2, 3, etc. I would like to filter on a text eventually. In the criteria cell A1, I only use one division at a time. I just want to know how to count scores for all divisions together if I need to. Can this be done? "Peo Sjoblom" wrote: I am not sure I understand, which column is the division? -- Regards, Peo Sjoblom "JLewis" wrote in message ... One more thing...(I know, I'm needy!). If I wanted to see for instance, all divisions, what would I put in the criteria cell? I know for the ratings, I could put 1 and 2000 or something like that, given ratings are between 1 and 1100. But divisions are different. Thanks again. "JLewis" wrote: Okay, I have finally gotten it to work. I had the criteria cells mixed up. I entered criteria for rating in the division cell. Yeah! It works! You are a genius. Thank you very much ! "JLewis" wrote: Yes, I did catch my typo. I was wondering in the formula, where does it state what to count? I ran a formula analysis and I see the true/false of the criteria search, but it doesn't seem to be counting anything in the D column where it should. "Peo Sjoblom" wrote: I noticed you wrote A1, A2 and A3 whereas your formula showed BA3 If you really meant A3 instead and if you pasted in the formula I gave you that might explain it. If that's the case change BA3 to A3 at the end The formula I gave you will count where D3:D808 equals D2-1 AND A3:A808 equals A1 AND where B3:B808 is greater than A2 AND less than either BA3 or A3 whichever it is supposed to be. -- Regards, Peo Sjoblom "JLewis" wrote in message ... It only gave me a "0". When I know for the criteria there should be 282 Birdies. I really appreciate the help! "Peo Sjoblom" wrote: Try =SUMPRODUCT(--(D3:D808=D2-1),--(A3:A808=A1),--(B3:B808A2),--(B3:B808<BA3)) -- Regards, Peo Sjoblom "JLewis" wrote in message ... Okay, this works great for criteria that references the data I'm counting. What about criteria based on another column? I have columns for division, rating, name, and for holes 1-18. I also have the first row that gives the hole pars. I have a formula for reporting birdies, bogeys, etc. But I'd like to more criteria to filter the data. Here is what I have in mind, but it will not work. =COUNTIFS(D3:D808,D2-1,A3:A808,"="&A1,B3:B808,""&A2,B3:B808,"<"&BA3 "D2-1" references the par for the hole less one. Essentially a birdie. A1,A2,A3 cells are for defining the filter criteria. This should give me all of the birdies for the players' scores that fit the criteria, shouldn't it? I like how you used the formula for being able to use an earlier form of Excel. I don't know that it would be necessary, but it is still nice to know that I could. "Peo Sjoblom" wrote: Yes it would, however I would rather use this instead =SUMPRODUCT(--(F3:F203A1),--(F3:F203<A2)) since it is compatible with earlier versions -- Regards, Peo Sjoblom |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create filter using criteria from other cells.
Yes, assume you put the different divisions as text in let's say H1:H10 (I
don't know how many there can be but keep in mind that the bigger size the slower spreadsheet when it comes to these kind of formulas) then this would count it =SUMPRODUCT(--(D3:D808=D2-1),--(ISNUMBER(MATCH(A3:A808,H1:H10,0))),--(B3:B808A2),--(B3:B808<A3)) Good luck -- Regards, Peo Sjoblom "JLewis" wrote in message ... A:3:A808. This could be a text, like Pro Amateur, Junior, Women, etc. For my purposes right now, I have it as a number, like 1, 2, 3, etc. I would like to filter on a text eventually. In the criteria cell A1, I only use one division at a time. I just want to know how to count scores for all divisions together if I need to. Can this be done? "Peo Sjoblom" wrote: I am not sure I understand, which column is the division? -- Regards, Peo Sjoblom "JLewis" wrote in message ... One more thing...(I know, I'm needy!). If I wanted to see for instance, all divisions, what would I put in the criteria cell? I know for the ratings, I could put 1 and 2000 or something like that, given ratings are between 1 and 1100. But divisions are different. Thanks again. "JLewis" wrote: Okay, I have finally gotten it to work. I had the criteria cells mixed up. I entered criteria for rating in the division cell. Yeah! It works! You are a genius. Thank you very much ! "JLewis" wrote: Yes, I did catch my typo. I was wondering in the formula, where does it state what to count? I ran a formula analysis and I see the true/false of the criteria search, but it doesn't seem to be counting anything in the D column where it should. "Peo Sjoblom" wrote: I noticed you wrote A1, A2 and A3 whereas your formula showed BA3 If you really meant A3 instead and if you pasted in the formula I gave you that might explain it. If that's the case change BA3 to A3 at the end The formula I gave you will count where D3:D808 equals D2-1 AND A3:A808 equals A1 AND where B3:B808 is greater than A2 AND less than either BA3 or A3 whichever it is supposed to be. -- Regards, Peo Sjoblom "JLewis" wrote in message ... It only gave me a "0". When I know for the criteria there should be 282 Birdies. I really appreciate the help! "Peo Sjoblom" wrote: Try =SUMPRODUCT(--(D3:D808=D2-1),--(A3:A808=A1),--(B3:B808A2),--(B3:B808<BA3)) -- Regards, Peo Sjoblom "JLewis" wrote in message ... Okay, this works great for criteria that references the data I'm counting. What about criteria based on another column? I have columns for division, rating, name, and for holes 1-18. I also have the first row that gives the hole pars. I have a formula for reporting birdies, bogeys, etc. But I'd like to more criteria to filter the data. Here is what I have in mind, but it will not work. =COUNTIFS(D3:D808,D2-1,A3:A808,"="&A1,B3:B808,""&A2,B3:B808,"<"&BA3 "D2-1" references the par for the hole less one. Essentially a birdie. A1,A2,A3 cells are for defining the filter criteria. This should give me all of the birdies for the players' scores that fit the criteria, shouldn't it? I like how you used the formula for being able to use an earlier form of Excel. I don't know that it would be necessary, but it is still nice to know that I could. "Peo Sjoblom" wrote: Yes it would, however I would rather use this instead =SUMPRODUCT(--(F3:F203A1),--(F3:F203<A2)) since it is compatible with earlier versions -- Regards, Peo Sjoblom |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create filter using criteria from other cells.
Oh yeah! That works great. This spreadsheet is finally functional! I really
owe you! Thanks for all of your help. "Peo Sjoblom" wrote: Yes, assume you put the different divisions as text in let's say H1:H10 (I don't know how many there can be but keep in mind that the bigger size the slower spreadsheet when it comes to these kind of formulas) then this would count it =SUMPRODUCT(--(D3:D808=D2-1),--(ISNUMBER(MATCH(A3:A808,H1:H10,0))),--(B3:B808A2),--(B3:B808<A3)) Good luck -- Regards, Peo Sjoblom "JLewis" wrote in message ... A:3:A808. This could be a text, like Pro Amateur, Junior, Women, etc. For my purposes right now, I have it as a number, like 1, 2, 3, etc. I would like to filter on a text eventually. In the criteria cell A1, I only use one division at a time. I just want to know how to count scores for all divisions together if I need to. Can this be done? "Peo Sjoblom" wrote: I am not sure I understand, which column is the division? -- Regards, Peo Sjoblom "JLewis" wrote in message ... One more thing...(I know, I'm needy!). If I wanted to see for instance, all divisions, what would I put in the criteria cell? I know for the ratings, I could put 1 and 2000 or something like that, given ratings are between 1 and 1100. But divisions are different. Thanks again. "JLewis" wrote: Okay, I have finally gotten it to work. I had the criteria cells mixed up. I entered criteria for rating in the division cell. Yeah! It works! You are a genius. Thank you very much ! "JLewis" wrote: Yes, I did catch my typo. I was wondering in the formula, where does it state what to count? I ran a formula analysis and I see the true/false of the criteria search, but it doesn't seem to be counting anything in the D column where it should. "Peo Sjoblom" wrote: I noticed you wrote A1, A2 and A3 whereas your formula showed BA3 If you really meant A3 instead and if you pasted in the formula I gave you that might explain it. If that's the case change BA3 to A3 at the end The formula I gave you will count where D3:D808 equals D2-1 AND A3:A808 equals A1 AND where B3:B808 is greater than A2 AND less than either BA3 or A3 whichever it is supposed to be. -- Regards, Peo Sjoblom "JLewis" wrote in message ... It only gave me a "0". When I know for the criteria there should be 282 Birdies. I really appreciate the help! "Peo Sjoblom" wrote: Try =SUMPRODUCT(--(D3:D808=D2-1),--(A3:A808=A1),--(B3:B808A2),--(B3:B808<BA3)) -- Regards, Peo Sjoblom "JLewis" wrote in message ... Okay, this works great for criteria that references the data I'm counting. What about criteria based on another column? I have columns for division, rating, name, and for holes 1-18. I also have the first row that gives the hole pars. I have a formula for reporting birdies, bogeys, etc. But I'd like to more criteria to filter the data. Here is what I have in mind, but it will not work. =COUNTIFS(D3:D808,D2-1,A3:A808,"="&A1,B3:B808,""&A2,B3:B808,"<"&BA3 "D2-1" references the par for the hole less one. Essentially a birdie. A1,A2,A3 cells are for defining the filter criteria. This should give me all of the birdies for the players' scores that fit the criteria, shouldn't it? I like how you used the formula for being able to use an earlier form of Excel. I don't know that it would be necessary, but it is still nice to know that I could. "Peo Sjoblom" wrote: Yes it would, however I would rather use this instead =SUMPRODUCT(--(F3:F203A1),--(F3:F203<A2)) since it is compatible with earlier versions -- Regards, Peo Sjoblom |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create filter using criteria from other cells.
My pleasure
-- Regards, Peo Sjoblom "JLewis" wrote in message ... Oh yeah! That works great. This spreadsheet is finally functional! I really owe you! Thanks for all of your help. "Peo Sjoblom" wrote: Yes, assume you put the different divisions as text in let's say H1:H10 (I don't know how many there can be but keep in mind that the bigger size the slower spreadsheet when it comes to these kind of formulas) then this would count it =SUMPRODUCT(--(D3:D808=D2-1),--(ISNUMBER(MATCH(A3:A808,H1:H10,0))),--(B3:B808A2),--(B3:B808<A3)) Good luck -- Regards, Peo Sjoblom "JLewis" wrote in message ... A:3:A808. This could be a text, like Pro Amateur, Junior, Women, etc. For my purposes right now, I have it as a number, like 1, 2, 3, etc. I would like to filter on a text eventually. In the criteria cell A1, I only use one division at a time. I just want to know how to count scores for all divisions together if I need to. Can this be done? "Peo Sjoblom" wrote: I am not sure I understand, which column is the division? -- Regards, Peo Sjoblom "JLewis" wrote in message ... One more thing...(I know, I'm needy!). If I wanted to see for instance, all divisions, what would I put in the criteria cell? I know for the ratings, I could put 1 and 2000 or something like that, given ratings are between 1 and 1100. But divisions are different. Thanks again. "JLewis" wrote: Okay, I have finally gotten it to work. I had the criteria cells mixed up. I entered criteria for rating in the division cell. Yeah! It works! You are a genius. Thank you very much ! "JLewis" wrote: Yes, I did catch my typo. I was wondering in the formula, where does it state what to count? I ran a formula analysis and I see the true/false of the criteria search, but it doesn't seem to be counting anything in the D column where it should. "Peo Sjoblom" wrote: I noticed you wrote A1, A2 and A3 whereas your formula showed BA3 If you really meant A3 instead and if you pasted in the formula I gave you that might explain it. If that's the case change BA3 to A3 at the end The formula I gave you will count where D3:D808 equals D2-1 AND A3:A808 equals A1 AND where B3:B808 is greater than A2 AND less than either BA3 or A3 whichever it is supposed to be. -- Regards, Peo Sjoblom "JLewis" wrote in message ... It only gave me a "0". When I know for the criteria there should be 282 Birdies. I really appreciate the help! "Peo Sjoblom" wrote: Try =SUMPRODUCT(--(D3:D808=D2-1),--(A3:A808=A1),--(B3:B808A2),--(B3:B808<BA3)) -- Regards, Peo Sjoblom "JLewis" wrote in message ... Okay, this works great for criteria that references the data I'm counting. What about criteria based on another column? I have columns for division, rating, name, and for holes 1-18. I also have the first row that gives the hole pars. I have a formula for reporting birdies, bogeys, etc. But I'd like to more criteria to filter the data. Here is what I have in mind, but it will not work. =COUNTIFS(D3:D808,D2-1,A3:A808,"="&A1,B3:B808,""&A2,B3:B808,"<"&BA3 "D2-1" references the par for the hole less one. Essentially a birdie. A1,A2,A3 cells are for defining the filter criteria. This should give me all of the birdies for the players' scores that fit the criteria, shouldn't it? I like how you used the formula for being able to use an earlier form of Excel. I don't know that it would be necessary, but it is still nice to know that I could. "Peo Sjoblom" wrote: Yes it would, however I would rather use this instead =SUMPRODUCT(--(F3:F203A1),--(F3:F203<A2)) since it is compatible with earlier versions -- Regards, Peo Sjoblom |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy only visible cells after filter is applied/ sum after filter | Excel Worksheet Functions | |||
Advanced Filter for multiple criteria, including blank cells | Excel Worksheet Functions | |||
Create macro to filter on multiple criteria | Excel Worksheet Functions | |||
How to Filter cells and save the file with certain criteria? | New Users to Excel | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions |