| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Is it possible to do a conditional subtotal in Excel?
Is it possible to do a conditional subtotal? I have used the Conditional Sum
feature many times, but I want to do a Conditional Subtotal, and I can't seem to get it to work. Any Ideas? |
|
#2
|
|||
|
|||
|
Hi
do you mean a conditional sum based on a filtered list? -- Regards Frank Kabel Frankfurt, Germany "LTS_Bgobien" schrieb im Newsbeitrag ... Is it possible to do a conditional subtotal? I have used the Conditional Sum feature many times, but I want to do a Conditional Subtotal, and I can't seem to get it to work. Any Ideas? |
|
#3
|
|||
|
|||
|
Yes, I would like to do a conditional sum based on a filtered list, but I
would like the sum value to represent the values shown by the filter only. As far as I know only the subtotal function can do this, but I'm sure there must be another way. Any help is appreciated. "Frank Kabel" wrote: Hi do you mean a conditional sum based on a filtered list? -- Regards Frank Kabel Frankfurt, Germany "LTS_Bgobien" schrieb im Newsbeitrag ... Is it possible to do a conditional subtotal? I have used the Conditional Sum feature many times, but I want to do a Conditional Subtotal, and I can't seem to get it to work. Any Ideas? |
|
#4
|
|||
|
|||
|
Hi
try something like the following (summs all values in column C if column B conatins 'value'): =SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX($A$1:$A$10,1,1),ROW($A$1: $A$10)- ROW(INDEX($A$1:$A$10,1,1)),0))=1),--($B$1:$B$10="value"),$C$1:$C$10) -- Regards Frank Kabel Frankfurt, Germany "LTS_Bgobien" schrieb im Newsbeitrag ... Yes, I would like to do a conditional sum based on a filtered list, but I would like the sum value to represent the values shown by the filter only. As far as I know only the subtotal function can do this, but I'm sure there must be another way. Any help is appreciated. "Frank Kabel" wrote: Hi do you mean a conditional sum based on a filtered list? -- Regards Frank Kabel Frankfurt, Germany "LTS_Bgobien" schrieb im Newsbeitrag ... Is it possible to do a conditional subtotal? I have used the Conditional Sum feature many times, but I want to do a Conditional Subtotal, and I can't seem to get it to work. Any Ideas? |
|
#5
|
|||
|
|||
|
Looks like you have something here, but i'm having trouble getting my numbers
right. Here is what I have: 1 A B C D E F G H 2 Year $ Value Date Value .. .. .. 200 I need to sum column F value's according to a date condition in column G. I can do this using a SUMIF Function, but I want my Sum total to change if I filter by year(column A), or any of my other columns. My rows go down to 200. "Frank Kabel" wrote: Hi try something like the following (summs all values in column C if column B conatins 'value'): =SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX($A$1:$A$10,1,1),ROW($A$1: $A$10)- ROW(INDEX($A$1:$A$10,1,1)),0))=1),--($B$1:$B$10="value"),$C$1:$C$10) -- Regards Frank Kabel Frankfurt, Germany "LTS_Bgobien" schrieb im Newsbeitrag ... Yes, I would like to do a conditional sum based on a filtered list, but I would like the sum value to represent the values shown by the filter only. As far as I know only the subtotal function can do this, but I'm sure there must be another way. Any help is appreciated. "Frank Kabel" wrote: Hi do you mean a conditional sum based on a filtered list? -- Regards Frank Kabel Frankfurt, Germany "LTS_Bgobien" schrieb im Newsbeitrag ... Is it possible to do a conditional subtotal? I have used the Conditional Sum feature many times, but I want to do a Conditional Subtotal, and I can't seem to get it to work. Any Ideas? |
|
#6
|
|||
|
|||
|
Hi
=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX($A$1:$A$200,1,1),ROW($A$1 :$A$200 )- ROW(INDEX($A$1:$A$200,1,1)),0))=1),--($G$1:$G$200=DATE(2004,1,1)),$F$1 :$F$200) If this does not work you may post your current sUMIF formula -- Regards Frank Kabel Frankfurt, Germany "LTS_Bgobien" schrieb im Newsbeitrag ... Looks like you have something here, but i'm having trouble getting my numbers right. Here is what I have: 1 A B C D E F G H 2 Year $ Value Date Value . . . 200 I need to sum column F value's according to a date condition in column G. I can do this using a SUMIF Function, but I want my Sum total to change if I filter by year(column A), or any of my other columns. My rows go down to 200. "Frank Kabel" wrote: Hi try something like the following (summs all values in column C if column B conatins 'value'): =SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX($A$1:$A$10,1,1),ROW($A$1: $A$10)- ROW(INDEX($A$1:$A$10,1,1)),0))=1),--($B$1:$B$10="value"),$C$1:$C$10) -- Regards Frank Kabel Frankfurt, Germany "LTS_Bgobien" schrieb im Newsbeitrag ... Yes, I would like to do a conditional sum based on a filtered list, but I would like the sum value to represent the values shown by the filter only. As far as I know only the subtotal function can do this, but I'm sure there must be another way. Any help is appreciated. "Frank Kabel" wrote: Hi do you mean a conditional sum based on a filtered list? -- Regards Frank Kabel Frankfurt, Germany "LTS_Bgobien" schrieb im Newsbeitrag ... Is it possible to do a conditional subtotal? I have used the Conditional Sum feature many times, but I want to do a Conditional Subtotal, and I can't seem to get it to work. Any Ideas? |
|
#7
|
|||
|
|||
|
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
| How do I set more than 3 conditional formats in Excel | Excel Discussion (Misc queries) | |||
| Conditional formatting not available in Excel | Excel Discussion (Misc queries) | |||
| Conditional Formats in Excel | Excel Worksheet Functions | |||
| Conditional Hide function for Excel | Excel Worksheet Functions | |||
Linear Mode
