Home |
Search |
Today's Posts |
#1
|
|||
|
|||
conditional subtotal counting
Please help me. Here is a very simplified example of my spreadsheet.
A B C D E 1 Text Subject Food Drink Reason 2 A1a A1 1 12 Work 3 A1b A1 0 4 Work 4 A3 A3 3 0 Pain 5 A5a A5 0 0 Work 6 A5b A5 0 0 Work 7 A5c A5 0 0 Work I have formulae for the following two types of totals. The "count by text" represents all the number of texts which contain words relating to Food or Drink. Total Occurrences: 4 16 Count by Text: 2 2 But how can I get the three sets of totals below? Count by Subject 2 1 Pain by Subject 1 0 Work by Subject 1 1 How can I get counts by subjects? (I know I could use the "Subtotal" wizard, and then check for the word "Total", but this would not enable me to also use the "Reason" conditions.) Texts are unique per row (at least, as far as this example is concerned). Each subject may have written more than one text. Food an Drink are categories of words, and columns C and D show the number of times such words occur in a text. Each subject may have only one "Reason" attribute; but a "reason" attribute may pertain to more than one subject. Any help would be appreciated. JessJ -- Jess (XP Home SP/2 OEM. HDD1:Windows NTFS 280GB; Fat32 1GB; Linux 19GB. HDD2: NTFS 80GB. HDD3: NTFS 40GB. P4 HT. 512MB) [Please note: Display name changed from Jess. I want to be unique!] |
#2
|
|||
|
|||
conditional subtotal counting
I'd probably use a Pivot Table to evaluate this.
http://www.peltiertech.com/Excel/Pivots/pivotstart.htm "JessJ" wrote in message ... Please help me. Here is a very simplified example of my spreadsheet. A B C D E 1 Text Subject Food Drink Reason 2 A1a A1 1 12 Work 3 A1b A1 0 4 Work 4 A3 A3 3 0 Pain 5 A5a A5 0 0 Work 6 A5b A5 0 0 Work 7 A5c A5 0 0 Work I have formulae for the following two types of totals. The "count by text" represents all the number of texts which contain words relating to Food or Drink. Total Occurrences: 4 16 Count by Text: 2 2 But how can I get the three sets of totals below? Count by Subject 2 1 Pain by Subject 1 0 Work by Subject 1 1 How can I get counts by subjects? (I know I could use the "Subtotal" wizard, and then check for the word "Total", but this would not enable me to also use the "Reason" conditions.) Texts are unique per row (at least, as far as this example is concerned). Each subject may have written more than one text. Food an Drink are categories of words, and columns C and D show the number of times such words occur in a text. Each subject may have only one "Reason" attribute; but a "reason" attribute may pertain to more than one subject. Any help would be appreciated. JessJ -- Jess (XP Home SP/2 OEM. HDD1:Windows NTFS 280GB; Fat32 1GB; Linux 19GB. HDD2: NTFS 80GB. HDD3: NTFS 40GB. P4 HT. 512MB) [Please note: Display name changed from Jess. I want to be unique!] |
#3
|
|||
|
|||
conditional subtotal counting
Hi Barb,
I'm not sure that a Pivot table will be appropriate. I've just had a look at the help link you gave me - thanks. I tried a Pivot table, and said it didn't have enough memory at one point. I need to be able to get all the grand total information out in one go. I actually have about 200 columns of results (such as "Food" and "Drink"), and another dozen or so columns of the subject attributes. And I have lots of rows too. Also, every text (i.e. written document) actually has 2 rows: one for raw figures ("r", see below) and one for percentage figures. And I have several other spreadsheets which are similar (each representing different sub-corpora). I have been toying with the following formula (which I could copy and paste into a row below my data). Column B is my "Subject" column. What do you think? =SUMPRODUCT(($B$2:$B$149=$B$3:$B$150)*($D$2:$D$149 ="r")*(E$2:E$1490)*1) It seems to work, but I can't check every application of it, and I would really appreciate an expert's opinion of its validity. Thanks, JessJ -- Jess (XP Home SP/2 OEM. HDD1:Windows NTFS 280GB; Fat32 1GB; Linux 19GB. HDD2: NTFS 80GB. HDD3: NTFS 40GB. P4 HT. 512MB) [Please note: Display name changed from Jess. I want to be unique!] "Barb Reinhardt" wrote: I'd probably use a Pivot Table to evaluate this. http://www.peltiertech.com/Excel/Pivots/pivotstart.htm |
#4
|
|||
|
|||
conditional subtotal counting
Apologies to anyone reading the above posting. My formula, as shown there,
did not work, and is absolute rubbish. Please put it down to stress! However, I have now solved the problem with a mixture of a filter and the subtotal aid. If anyone's interested, I shall add that I solved it by summing the rows containing the word "Total", and counting the non-zero totals in a SUMPRODUCT formula which also included the several conditions, and returned a count. It's not ideal, but it'll do until I can get into Visual Basic. jessj -- Jess (XP Home SP/2 OEM. HDD1:Windows NTFS 280GB; Fat32 1GB; Linux 19GB. HDD2: NTFS 80GB. HDD3: NTFS 40GB. P4 HT. 512MB) [Please note: Display name changed from Jess. I want to be unique!] "JessJ" wrote: Hi Barb, I'm not sure that a Pivot table will be appropriate. I've just had a look at the help link you gave me - thanks. I tried a Pivot table, and said it didn't have enough memory at one point. I need to be able to get all the grand total information out in one go. I actually have about 200 columns of results (such as "Food" and "Drink"), and another dozen or so columns of the subject attributes. And I have lots of rows too. Also, every text (i.e. written document) actually has 2 rows: one for raw figures ("r", see below) and one for percentage figures. And I have several other spreadsheets which are similar (each representing different sub-corpora). I have been toying with the following formula (which I could copy and paste into a row below my data). Column B is my "Subject" column. What do you think? =SUMPRODUCT(($B$2:$B$149=$B$3:$B$150)*($D$2:$D$149 ="r")*(E$2:E$1490)*1) It seems to work, but I can't check every application of it, and I would really appreciate an expert's opinion of its validity. Thanks, JessJ -- Jess (XP Home SP/2 OEM. HDD1:Windows NTFS 280GB; Fat32 1GB; Linux 19GB. HDD2: NTFS 80GB. HDD3: NTFS 40GB. P4 HT. 512MB) [Please note: Display name changed from Jess. I want to be unique!] "Barb Reinhardt" wrote: I'd probably use a Pivot Table to evaluate this. http://www.peltiertech.com/Excel/Pivots/pivotstart.htm |
#5
|
|||
|
|||
conditional subtotal counting
Sorry, regarding the above posting, I forgot to add that I also managed to
access values on the row above a subtotal with an range of one row less than the total range of cells. Enough already! Topic closed. jessj -- Jess (XP Home SP/2 OEM. HDD1:Windows NTFS 280GB; Fat32 1GB; Linux 19GB. HDD2: NTFS 80GB. HDD3: NTFS 40GB. P4 HT. 512MB) [Please note: Display name changed from Jess. I want to be unique!] "JessJ" wrote: Hi Barb, I'm not sure that a Pivot table will be appropriate. I've just had a look at the help link you gave me - thanks. I tried a Pivot table, and said it didn't have enough memory at one point. I need to be able to get all the grand total information out in one go. I actually have about 200 columns of results (such as "Food" and "Drink"), and another dozen or so columns of the subject attributes. And I have lots of rows too. Also, every text (i.e. written document) actually has 2 rows: one for raw figures ("r", see below) and one for percentage figures. And I have several other spreadsheets which are similar (each representing different sub-corpora). I have been toying with the following formula (which I could copy and paste into a row below my data). Column B is my "Subject" column. What do you think? =SUMPRODUCT(($B$2:$B$149=$B$3:$B$150)*($D$2:$D$149 ="r")*(E$2:E$1490)*1) It seems to work, but I can't check every application of it, and I would really appreciate an expert's opinion of its validity. Thanks, JessJ -- Jess (XP Home SP/2 OEM. HDD1:Windows NTFS 280GB; Fat32 1GB; Linux 19GB. HDD2: NTFS 80GB. HDD3: NTFS 40GB. P4 HT. 512MB) [Please note: Display name changed from Jess. I want to be unique!] "Barb Reinhardt" wrote: I'd probably use a Pivot Table to evaluate this. http://www.peltiertech.com/Excel/Pivots/pivotstart.htm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional counting with Excel | Excel Worksheet Functions | |||
conditional counting of autolist cells/rows | Excel Discussion (Misc queries) | |||
Conditional SUBTOTAL | Excel Discussion (Misc queries) | |||
Is there a command like subtotal but with a conditional? | Excel Worksheet Functions | |||
Is it possible to do a conditional subtotal in Excel? | Excel Worksheet Functions |