conditional subtotal counting

[JessJ]

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

 

[Barb Reinhardt]

I'd probably use a Pivot Table to evaluate this.

http://www.peltiertech.com/Excel/Pivots/pivotstart.htm

 

[JessJ]

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$149>0)*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

 

[JessJ]

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!]

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$149>0)*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!]

I'd probably use a Pivot Table to evaluate this.

http://www.peltiertech.com/Excel/Pivots/pivotstart.htm

 

[JessJ]

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!]

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$149>0)*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!]

I'd probably use a Pivot Table to evaluate this.

http://www.peltiertech.com/Excel/Pivots/pivotstart.htm