Page 1 of 1
| Another Excel question 22/11/2011 at 15:17 #23482 | |
Peter Bennet
5402 posts |
I have an array of data and I want to count the number of lines fulfilling a specific criteria- I use =COUNTIF(J2:J10000,"True"). Now if the table is filtered and I want to count only the visible data I've used =SUMPRODUCT(SUBTOTAL(3,OFFSET($J$2:$J$10000,ROW($J$2:$J$10000)-ROW($J$2),0,1)),--($J$2:$J$10000="True")) Then if I want to test for matching data in different columns I've used =SUMPRODUCT((J2:J10000="True")*(K2:K10000="True")) But what I now want to do is modify the third formula for filtered data and that's where I've run into a brick wall with my searches. Tried substituting for each bit of #3 a formula based on #2 above but that gives the multiple for what it finds in each column. Any ideas Thanks Peter I identify as half man half biscuit - crumbs! Log in to reply |
| Re: Another Excel question 22/11/2011 at 18:53 #23498 | |
CAP.
24 posts |
If I understand the problem correctly, here's what I would do: I would insert a column which evaluates if both the value in columns J and K are true: The formulas for each cell in this column need to be: =AND(J2,K2) =AND(J3,K3) =AND(J4,K4) and so on Then apply your 2nd formula to the column which holds the AND formulas. Note that for this to work you would need to replace "True" by TRUE both in the 2nd formula and in the field values of colums J and K. Hope this helps 
Log in to reply |
| Re: Another Excel question 22/11/2011 at 20:14 #23501 | |
|
Peter Bennet
5402 posts |
Right yes I see- I may have misled you slightly in the use of "true" which was intended to be a variable that was true in the sense of what I was searching for. So column J would be a lists of data (say) A,B C and K would be W,X,Y,Z then I'd have a drop-down list of the different possibilities so the Trues would in fact be whatever was selected (say) A and X or C and Y and so on. The filter is for dates because I only want C and Y matches for filtered dates. So. for example, what I want is between any given date pairs as filtered the number of cases where C and Y are true. I know I could just filter the complete parameters but I need to do more than one comparison at the same time and then plot a graph of the results. Anyway- thanks for you suggestion. Peter I identify as half man half biscuit - crumbs! Log in to reply |
| Re: Another Excel question 22/11/2011 at 20:57 #23502 | |
|
CAP.
24 posts |
A Pivotchart might help here. To create the pairs I would 'merge' the information from columns J and K into another column by: =J2&"-"&K2 =J3&"-"&K3 etc And then setup the Pivotchart to plot for each pair the number of occurences next to eachother. You could include the dates column as a filter in the Pivotchart. Log in to reply |
| Re: Another Excel question 22/11/2011 at 21:29 #23504 | |
|
Peter Bennet
5402 posts |
Hmm I was afraid pivot tables might enter the fray... Had a quick test and I can see it'll have potential. Like the consolidation method- I tend to use CONCATINATE but this looks simpler. Thanks Peter I identify as half man half biscuit - crumbs! Log in to reply |
| Re: Another Excel question 22/11/2011 at 22:49 #23507 | |
postal
5264 posts 
|
Peter I am having trouble understanding exactly what you want. I may have an answer using SUMPRODUCT if I have understood correctly. For example, I run a Fantasy Football League on an Excel file using a 15 man squad and into and out of which player transfers can be made throughout the season. The players are also categorised as Keepers, Defenders, Midfielders and Forwards and there are a number of permissible formations so that each team does not necessarily have the same number of players in each category. I have an error trap set up to determine whether the team is legal on any particular week as transfers in and out mean that there could be up to 39 players listed for one team. The trap works by using SUMPRODUCT to check the number of players in each positional category who have a "transferred-in" date before the particular date, and "transferred-out" date after the particular date. The formula in BI4 (column BI row 4 as this font is not explicit!) is of the format =SUMPRODUCT(--($G4:$G42<=$A$1),--($H4:$H42>=$A$1),--($D4:$D42=$BH4)) where G4:G42 and H4:H42 are the in and out dates for each player in the team, $A$1 is the date I wish to specify (although you could equally well use two different dates), D4:D42 list the positional category for each player and BH4 identifies the particular category I wish to count. I put this formula into 4 rows in column BI with BH containing GK, DE, MF and FW down the 4 rows. Typically this gives results of 2,5,5 and 3 to make the current 15 man squad out of the up to 39 listed. If that is the sort of thing you are after you can probably modify the formula to fit. If not, if you want to e-mail the file or relevant bit to me with details of exactly what output you need, I'll be able to work something up for you. “In life, there is always someone out there, who won’t like you, for whatever reason, don’t let the insecurities in their lives affect yours.” – Rashida Rowe Last edited: 22/11/2011 at 23:56 by postal Log in to reply |
| Re: Another Excel question 23/11/2011 at 09:39 #23509 | |
|
Peter Bennet
5402 posts |
This is my experimental sheet (no I can't upload so I'll email). thanks Peter I identify as half man half biscuit - crumbs! Log in to reply |
| Re: Another Excel question 23/11/2011 at 09:42 #23510 | |
|
postal
5264 posts
|
" said:This is my experimental sheetMaybe the admin has forgotten to add .xls and .xlsx to the permitted list of upload types 
“In life, there is always someone out there, who won’t like you, for whatever reason, don’t let the insecurities in their lives affect yours.” – Rashida Rowe Log in to reply |
| Re: Another Excel question 23/11/2011 at 10:07 #23511 | |
|
Peter Bennet
5402 posts |
No it's the system here the "add file" is disabled. Peter I identify as half man half biscuit - crumbs! Log in to reply |