SUMPRODUCT varying the criteria

[Rob Bickley]

I have a real brain teaser. I have a table of data which I would like
to sum but the criteria to sum by can change based on 3 options:

1. Use columns A-1 to A-3 soley
2. Use columns B-1 to B-3 soley
3. Use both columns

The criteria changes to specify which A or B columns to use...but
only one at a time.

The reference in A29 varies depening on the option selection - 1,2 or
3.

Option 1 - INDIRECT($B$17)=$D$17) in this exanple is France
Option 2 - INDIRECT($B$18)=$D$18) is Test
Option 3 - uses both so France and Test.

My problem is that the formula is large but I cannot find a way to
make it more efficient. Ideally I want a test of which option is used
which and then it will use INDIRECT($B$17)=$D$17) or
INDIRECT($B$18)=$D$18) or both. I have tried to show a blank in the
criteria table (ie. France would be blank if Option 2 selected....but
it just returns a Zero since there are no blank cells in my table.
Ideally I need to use <> if a blank value but I cannot see a way to
vary between = or <>.

Any help gratefully received.

Thanks
Rob

My formula;

=CHOOSE($A$29,SUMPRODUCT((INDIRECT($B$17)=$D$17)*(Period<=D20)*(Period>=D21)*($J$4:$L$4=$D$19)*($J$6:$L$14)),SUMPRODUCT((INDIRECT($B$18)=$D$18)*(Period<=D20)*(Period>=D21)*($J$4:$L$4=$D$19)*($J$6:$L$14)),SUMPRODUCT((INDIRECT($B$17)=$D$17)*(INDIRECT($B$18)=$D$18)*(Period<=D20)*(Period>=D21)*($J$4:$L$4=$D$19)*($J$6:$L$14)))

My Table;

Col A-1 is excel col. B to 600010 is col.J

Col A-1 Col A-2 Col A-3 Col B-1 Col B-2 Col B-3 Col B-4 PERIOD Account


ALL Britain B010 GLOBAL GBU CUO ACTU 1 56,149
ALL France FR010 GLOBAL TEST CUO TEST 10 78,004
ALL Britain B020 GLOBAL GBU CUO ACTU 11 72,513
ALL Britain B010 GLOBAL GBU CUO TEST 12 (128,214)
ALL Britain B010 GLOBAL GBU TEST ACTU 2 56,423
ALL France FR020 GLOBAL GBU CUO ACTU 3 (95,960)
ALL Spain SP010 GLOBAL GBU CUO ACTU 4 56,881
ALL Spain SP011 GLOBAL GBU CUO ACTU 5 56,881

My criteria;

Col A-2 France
Col B-4 TEST
Period 10
Period 4

 

[Debra Dalgleish]

I'm not sure that I understand your question, but if you have headings
and criteria in cells A1:B3, you could use the CHOOSE function, and
DSUM, to calculate the total. With a 1, 2, or 3 in cell A29, the formula
will select the correct criteria range:

=DSUM(D1:K36,5,CHOOSE(A29,A1:A3,B1:B3,A1:B3))

where the data is in the range D1:K36.

 

[Rob Bickley]

Thanks for the posting. I did originally use DSUM but because this
formula needs to work in individual columns and DSUM criteria not
flexible enough (ie. requires to be laid out across the columns in a
table format) I opted for SUMPRODUCT. Otherwise I would have to repeat
the whole DSUM criteria for each column, of which there will be many.
Unless someone knows how to get DSUM to work will criteria going down
the rows to save on space.

With SUMPRODUCT I can refer to criteria indiviually in any part of the
sheet. My criteria varying for 3 different options only applies to one
part of my overall criteria. CHOOSE works OK within the DSUM but not
within SUMPRODUCT unless some knows how. This is what I orginally
tried but it returns a #VALUE!

=SUMPRODUCT(CHOOSE(A29,(INDIRECT($B$17)=$D$17),(INDIRECT($B$18)=$D$18),(INDIRECT($B$17)=$D$17)*(INDIRECT($B$18)=$D$18))*(Period<=D20)*(Period>=D21)*($J$4:$L$4=$D$19)*($J$6:$L$14))


Maybe because the CHOOSE is working on text....I don't know!?

 

[Erich Neuwirth]

Did you consider using a pivot table for your problem?
Or is this not a viable option?

 

[Rob Bickley]

Yeah Pivot tables are powerful but the presentation sucks!

How pivot tables present the information and how most clients would
like the data presented are two different things.

Unless someone knows how to make pivot tables more presentable I have
to discount that option.

Its certainly an area where Microsoft could improve Excel.

Thanks for the suggestion anyway.

 

[Roger Govier]

Hi Rob

Yeah Pivot tables are powerful but the presentation sucks!

I can't agree with you totally there.
They can be made to look quite presentable.

However, in some circumstances, I want a layout which is quite different to
the layout from the PT and in those cases I use the PT to do the slog of the
calculations and use the GETPIVOTDATA function of Excel to pick off the
results into a layout that I have set up.