Shortening SUMPRODUCT formulas

  • Thread starter Thread starter Dluxe
  • Start date Start date
D

Dluxe

Hi all.

This is a re-post, in a sense, of a question I posted last week to this
forum.

I work at a college and use SUMPRODUCT formulas to total up all kinds of
stuff. For example, to calculate the number of people majoring in a
particular area (in this case, Social Sciences and Arts), I use the
following:
=SUMPRODUCT(((Major="African/African Am Studies")+(Major="Ancient
History")+(Major="Art")+(Major="Art History")+(Major="Asian
Studies")+(Major="Chinese")+(Major="Classics")+(Major="Classical
Studies")+(Major="Communication Theory")+(Major="Comparative
Literature")+(Major="Drama")+(Major="Education")+(Major="English")+(Major="F
ilm Studies")+(Major="French")+(Major="Government")+(Major="Health
Policy")+(Major="History")+(Major="Humanities")+(Major="Human Development &
Education")+(...etc..)+(Major="Visual Studies")+(Major="War and Peace
Studies"))*((Decision="BA")+(Decision="BB")+(Decision="BC"))*(Round="1"))

Some of these formulas are getting too long for excel. I posted asking if a
lookup function was the right way to fix it... A reply suggested the
following:
=SUMPRODUCT(--(Major={"African/African Am Studies","Ancient
History","Art","Art History",etc...}))

Which works for counting the majors by themselves, but I can't seem to the
other conditions in (like DECISION and ROUND) using the same format. I
tried something like this:
=SUMPRODUCT(((--(Major={"Economics","Finance","Inter'l Economic
Policy","Monetary & Financial Policy","Public
Economics"}))*(--(Decision={"BA","BC"}))*(Round="1")))

But as soon as I combine two of the --(Major={ things, I get an #N/A error.

Any thoughts/help appreciated.

Best, Dluxe
 
Seem like it may be easier to exclude <> vs =
Anyway try this
=(major={"a","b","c","etc"})
 
The problem is that your comparison arrays are different sizes, screwing up
the formula.

The best I could do was this, but it defeats part of your purpose

=SUMPRODUCT((Major={"Economics","Finance","Inter'l Economic
Policy","Monetary & Financial Policy","Public
Economics","Economics","Finance","Inter'l Economic Policy","Monetary &
Financial Policy","Public
Economics"})*(Decision={"BA","BA","BA","BA","BA","BC","BC","BC","BC","BC"})*
(Round={"1","1","1","1","1","1","1","1","1","1"}))

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
You could use DSUM. Type lists of your subjects, decisions, etc.
Then, create a category area in which you test the first row of data
against the lists. Leave the criteria area heading row blank, and in the
cell below, use COUNTIF formulas to test the data, e.g.:

<blank> <blank> Round
=COUNTIF($N$2:$N$30,E2) =COUNTIF($O$2:$O$3,B2) 1

In this example, the subject list is in cells N2:N30, and the subject is
in column E of the main table.

Then, enter a DSUM formula that refers to the database, the column you
want to sum, and the criteria area:

=DSUM(Database,"Units",K1:L2)
 
Well, the trick is that I have to look at majors for different subsets of
people (I'm feeding this spreadsheet with a ODBC pull from a database).

So, I need to be able to say
For people with THESE majors, from this ROUND, who we've coded THIS way.
 
Bob,

so am I correct in that if I include an array within one of the 'sections'
of the formula, the others must have arrays as well?

I was really hoping it was as easy as:
=SUMPRODUCT((--(Decision={stuff}))+(other condition)*(other condition)).

That's part of the reason I thought a lookup table might help. A table of
all majors with a code next to them that assigns them to a group. The
sumproduct would then look something like this:
=SUMPRODUCT((Major=(LOOKUP(SomeCells,SOMECODE))*(other condition)*(Other
condition))

But I couldnt get that to work either... And even the Excel bible didn't
have it *gasp*!!

Thoughts? B
 
Debra,

Thanks... Your answer seems to describe the kind of approach I was
envisioning with LOOKUPS. Though I'm still not clear how to proceed.

Can you clarify just a little on your message below. I'm a little unclear
on how the 'reference table' is set up.

Thanks, Me
 
If you look at the way SUMPRODUCT evaluates, it needs similar sized arrays
to do the SP on, as it multiples each element of each condition by its
relative element of the other condition arrays. Thus, if you have 10
comparison elements in condition 1, you need 10 in condition 2. But it is
not that simple either, because you need corresponding items for each
condition. So, if condition 1 has 3 comparison items, condition 2 has 2, and
condition 3 has 4, then each comparison needs 24 elements (2x3x4) so as to
be able to evaluate every combination.

There may be another way of doing it, but I don't about it.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Back
Top