SUBTOTAL and IF

[Chris Davison]

Morning all,

{"Name","Code","Amount";"John",1,5;"Paul",1,10;"George",1,15;"Ringo",1,20;"Bob",2,25;"John",2,30;"John",1,35;"John",1,40;"John",3,45}

I'm filtering column A so it only shows "John". We're all familiar with the =SUBTOTAL function that will sum only visible rows, but I'd like to subtotal only if the code is 1.

My ultimate goal is to have totals for each code which change as I filter by each name in column A.

(If it's not possible, I'll revert back to a pivot table)

so basically, can I use =SUBTOTAL with a single criteria ? (like =SUMIF does)

many thanks
Chris


edit - answer in the above example should be 80



[ This Message was edited by: Chris Davison on 2002-03-13 05:34 ]

[Barrie Davidson]

On 2002-03-13 05:33, Chris Davison wrote:
Morning all,

{"Name","Code","Amount";"John",1,5;"Paul",1,10;"George",1,15;"Ringo",1,20;"Bob",2,25;"John",2,30;"John",1,35;"John",1,40;"John",3,45}

I'm filtering column A so it only shows "John". We're all familiar with the =SUBTOTAL function that will sum only visible rows, but I'd like to subtotal only if the code is 1.

My ultimate goal is to have totals for each code which change as I filter by each name in column A.

(If it's not possible, I'll revert back to a pivot table)

so basically, can I use =SUBTOTAL with a single criteria ? (like =SUMIF does)

many thanks
Chris


edit - answer in the above example should be 80



[ This Message was edited by: Chris Davison on 2002-03-13 05:34 ]

Chris, why not filter on the Code as well?

Regards,

[Chris Davison]

thanks Barrie

Unfortunately, I will be showing all the other data, it's just the names I'll be filtering

[Barrie Davidson]

On 2002-03-13 05:55, Chris Davison wrote:
thanks Barrie

Unfortunately, I will be showing all the other data, it's just the names I'll be filtering

Sorry Chris, as far as I know (with my limited knowledge, and I'm willing to bet Aladin or Mark W will be able to come up with something!) you can't do this so you'll have to resort back to your pivot table.

Regards,

[Chris Davison]

Hey, no worries !

(Previously, I had been doing seperate tables of names in seperate worksheets, pasted from my accounting system - so I figured rather than doing 30 seperate pastes, I could do a single paste and then just use criteria and filter to cut down the 2 days it takes to just maybe a few hours whilst also, for presentation purposes, I didn't want any ugly pivot tables)

It could just be I started off in the wrong direction designwise so may have to go a different way

thanks though, much appreciated
Chris

[Aladin Akyurek]

On 2002-03-13 05:33, Chris Davison wrote:
Morning all,

{"Name","Code","Amount";"John",1,5;"Paul",1,10;"George",1,15;"Ringo",1,20;"Bob",2,25;"John",2,30;"John",1,35;"John",1,40;"John",3,45}

I'm filtering column A so it only shows "John". We're all familiar with the =SUBTOTAL function that will sum only visible rows, but I'd like to subtotal only if the code is 1.

My ultimate goal is to have totals for each code which change as I filter by each name in column A.

(If it's not possible, I'll revert back to a pivot table)

so basically, can I use =SUBTOTAL with a single criteria ? (like =SUMIF does)

many thanks
Chris
[img]/board/images/smiles/icon_smile.gif[/img]

edit - answer in the above example should be 80

[ This Message was edited by: Chris Davison on 2002-03-13 05:34 ]

Chris,

Beside PivotTables, you have also formula-based approaches to the task you've set up for us. I don't believe SUBTOTAL can be of any help in conditional summing.

I'll skip SUMPRODUCT/array-formulas.

I'll assume that A1:C10 houses the sample data you provided.

Select A1:C10 and name it Dbase (via the Name Box).

Activate A2.
Activate Data|Filter|Advanced Filter.
Check 'Copy to another location'.
Set the 'Input range' to $A$1:$A$10.
Set 'Copy to' to $J$1.
Check 'Unique records only'.
Click OK.

Activate B2.
Activate Data|Filter|Advanced Filter.
Check 'Copy to another location'.
Set the 'Input range' to $B$1:$B$10.
Set 'Copy to' to $K$1.
Check 'Unique records only'.
Click OK.

Select all of the cells filtered to J1 from J2 on.
Name it Names.
Select all of the cells filtered to K1 from K2 on.
Name it Codes.

I'd suggest moving Names and Codes to a separate sheet you could call Admin.

In E1:F1 enter:

{"Name","Code"}

Activate E2.
Activate Data|Validation.
Choose 'List' for Allow.
Enter as source:

=Names

Activate F2.
Activate Data|Validation.
Choose 'List' for Allow.
Enter as source:

=Codes

Select E1:F2 and name it Conditions (via the Name Box).

In G2 enter:

=DSUM(Dbase,3,Conditions)

Experiment.

Selecting John in E2 and 1 in F2, I get 80 in G2.

Aladin

[Chris Davison]

thanks for your time on this Aladin, I think it outvotes my workaround.... I just popped some columns to the right which said "if it's code 1 then put in the value" "if it's code 2 then put in the value" etc etc then had subtotals at the top of each column, which were referenced back, which worked but left me with zillions of formulae and a lag time when I filtered.

With a maximum of a dozen names and 30 codes, (both static), I now only need 300ish DSUM formula, rather than 1000 rows of my 30-column workaround

can't wait to use this Thursday morning !

cheers mate
Chris


[ This Message was edited by: Chris Davison on 2002-03-13 13:59 ]