Shortening SUMPRODUCT formulas

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


0
Dluxe
3/1/2005 1:32:13 PM
excel 39879 articles. 2 followers. Follow

8 Replies
590 Views

Similar Articles

[PageSpeed] 2

Seem like it may be easier to exclude <> vs =
Anyway try this
=(major={"a","b","c","etc"})

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Dluxe" <dluxe@gmailDOTcom> wrote in message
news:u8JdVMmHFHA.1948@TK2MSFTNGP14.phx.gbl...
> 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
>
>


0
Don
3/1/2005 1:52:14 PM
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)


"Dluxe" <dluxe@gmailDOTcom> wrote in message
news:u8JdVMmHFHA.1948@TK2MSFTNGP14.phx.gbl...
> 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
>
>


0
bob.phillips1 (6510)
3/1/2005 2:01:09 PM
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)

Dluxe wrote:
> 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
> 
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
3/1/2005 2:17:31 PM
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.



"Don Guillett" <donaldb@281.com> wrote in message
news:uhUrhXmHFHA.572@tk2msftngp13.phx.gbl...
> Seem like it may be easier to exclude <> vs =
> Anyway try this
> =(major={"a","b","c","etc"})
>
> -- 
> Don Guillett
> SalesAid Software
> donaldb@281.com
> "Dluxe" <dluxe@gmailDOTcom> wrote in message
> news:u8JdVMmHFHA.1948@TK2MSFTNGP14.phx.gbl...
> > 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
> >
> >
>
>


0
Dluxe
3/1/2005 4:55:08 PM
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
"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:u1OLgcmHFHA.3196@TK2MSFTNGP15.phx.gbl...
> 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)
>
>
> "Dluxe" <dluxe@gmailDOTcom> wrote in message
> news:u8JdVMmHFHA.1948@TK2MSFTNGP14.phx.gbl...
> > 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
> >
> >
>
>


0
Dluxe
3/1/2005 4:58:52 PM
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
"Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
news:4224797B.7090006@contexturesXSPAM.com...
> 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)
>
> Dluxe wrote:
> > 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
> >
> >
>
>
> -- 
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>


0
Dluxe
3/1/2005 5:16:55 PM
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)


"Dluxe" <dluxe@gmailDOTcom> wrote in message
news:%23bMaz$nHFHA.3208@TK2MSFTNGP10.phx.gbl...
> 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
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:u1OLgcmHFHA.3196@TK2MSFTNGP15.phx.gbl...
> > 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)
> >
> >
> > "Dluxe" <dluxe@gmailDOTcom> wrote in message
> > news:u8JdVMmHFHA.1948@TK2MSFTNGP14.phx.gbl...
> > > 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
> > >
> > >
> >
> >
>
>


0
bob.phillips1 (6510)
3/1/2005 5:37:56 PM
I've added a sample file to my website, that will show you how you can 
set up the sheet. The criteria area and lists could be on separate sheets.

   http://www.contextures.com/excelfiles.html

Under Functions, look for 'Database Functions'

Dluxe wrote:
> 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
> "Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
> news:4224797B.7090006@contexturesXSPAM.com...
> 
>>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)
>>
>>Dluxe wrote:
>>
>>>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
>>>
>>>
>>
>>
>>-- 
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
> 
> 
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
3/1/2005 6:22:27 PM
Reply:

Similar Artilces:

Tracking changes to a cell containing formula
I want to track changes to cells because a formula calculates a ne value, not because i changed directly the value of a cell. the excel help indicate the following: Cells that change because a formula calculates a new value---> To fin cells that change due to recalculation, use the tools on the Auditin toolbar. How to do that? The formula auditing toolbar doesn't seem to hav something like that... Thanks for your tim -- Message posted from http://www.ExcelForum.com Hi have a look at 'Tools - Track changes' -- Regards Frank Kabel Frankfurt, Germany > I want to track ch...

Formula #6
Can any one help me, its simple if you know how. What is the formula to make one cell absolute to another. -- Many Thanks Trevor Hi Don't really understand the question - but here goes. If you want a cell to always show the same as another, type = in the first cell and then click the second with your mouse and press Enter. This will 'link' the two cells, and if you move the second, the first will stay linked to it. If you want to link to the cell that is always a row up and a column left (for example) from the first, use the OFFSET function. Hope this helps. -- Andy. &...

Invisible Formula in Cells !!!!!!!!!!!!!!!!!!
I have this strang excel file. When I enter formular into it, it don't show thae value but when I enter anything else in the cell, that is visible. The formula is in the cell, its just not visible. On Apr 21, 7:41=A0am, Ayo <A...@discussions.microsoft.com> wrote: > I have this strang excel file. When I enter formular into it, it don't sh= ow > thae value but when I enter anything else in the cell, that is visible. T= he > formula is in the cell, its just not visible. Probably the sheet is protected and Hidden check box of Protection tab of Format Cell dial...

Formula Help #37
I am looking for a formula that I can put in a cell to add up all the water in A1 to A5 that has clean next to it in B1 to B5. Could someone please tell me how to do this. a b c d e 1 Water Clean 2 Water Clean 3 Soap Dish 4 Soap Laundry 5 Water Spray =SUMPRODUCT((A1:A5="Water")*(B1:B5="Clean")) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk "Jeremy" <Jeremy@discussions.micr...

Lookup Value based on a formula
Hi all I am looking up a value in a table that is based around other formulas. When creating the Lookup the formula window shows the correct value. When the formula is entered the result is DIV/0!. Is there an easy way around it? or will I need the table based on hard values? Cheers Show us the actual formulas best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Cogeco news" <tmarshall3@cogeco.ca> wrote in message news:TZGGh.419$yc.257@read2.cgocable.net... > Hi all > > I am looking up a value in a table that is based around o...

Formula Question #21
I'm working with payroll data. I have a formula that displays payroll data that's based on a payroll date. Until the payroll date is reached the cell reads FALSE. Is there a way to display a 0 instead? This is the formula =IF(A30>=A36,SUM('3-15'!X38 +'3-15'!AB38 + '3-15'!AF38 + '3-15'!AJ38 + '3-15'!BT38)). Cell A30 contains the current date. I have another formula that adds the number of hours worked in the 2 pay periods in the month. If the hours add up to <100 a message appears. The problem is it's using the same cells to calul...

Hiding Formula's
Hi In Excel is it possible to hide or lock a formula e.g. =IF(A2=Rate_30,A2,"") so members of staff don't mess with it or delete it by accident - often have to Sort the sheet during business hours - so have tried sheet protection and locking and hiding this is a bit cumbersome is there anything easier and quicker. Mully Two step process: 1) select the cells that you want to be available to users to enter data; use Format|Cells, open Protection tab and uncheck the Locked box 2) Use Tools|Protection and protect the sheet. Since your aim is to prevent accidents, do not ...

Basic Formula question
I'm trying to set up a simple employee time sheet and I want to list each day showing the employee working say "8 to 6" but then at the end of the week have excel total up the hours. But I'm not sure how to get the cell showing "8 to 6" to translate into a numerical value so I can get it to total up at the end of the week. I would set up two columns: Start time and End time. Then hours worked is: =(EndTime - StartTime) * 24 Regards Fred "Matthew" <billgates@microsoft.com> wrote in message news:hk9hnl$knl$1@news.eternal-septem...

If formula problem
I need the formula to do the following: if i mark an "x" in E18 if i sell motorbikes today, and the total wil automatically be C18*D18*E9=total. If i buy motorbikes today i need th same operation, but the total will be in minus(C18*D18*E10=-total). need a formula that covers all the "x" (for today and tomorrow), bu always just one "X" in the same line I used something like this formula before, with "index" and "match operations. Any suggestions guys? Phoeni Attachment filename: if formula.xls Download attachment: h...

Lookup and count in the same formula
Hello, I need a formula that looks up a value in row 1 on sheet2 and returns the count of a range in the colmun that matches the lookup in sheet1. For example: Sheet2 - Feed Sheet Smithfield RSM John Doe ASM Jenn Smith ASM2 Brian Smith So Smithfield has 1 RSM and 2 ASMs Sheet1 RSM ASM Smithfield 1 2 So I want a formula to return the count of how many RSM's and ASM's are in a specific location. I have this: =(HLOOKUP(A1,Sheet2!A1:A6,(COUNTA(Sheet2!A5:A6)),FALSE)) but it's just retur...

Formula is not working
Hi There: New bee. I have data like: A------B-----C----D-----E----F------G H I 1990--1-----------3-------6---------8 1991--1-----------9-------2---------7 1992--2-----------5-------7---------1 Col C will have data diff(G1990-B1991).That means for previous year. I was trying: range("c2:c3").formular1c1 = "=RC[4].offset(-1,0) - RC[-1]" --Not working,Having application error I will try the following way. But its too long. I need to use a rang and a formula(if possible). cell(2,3).value = cell(2,3).offset(-1,3).value cell(2,3).offset(0,-1).value cell(3,3).value =...

Formula Won't Work
Hi all, Aim: to select from a drop down box one's salary range and then spit out the marginal tax rate. The rates are as follows: Australian 2006/2007 Income Tax Rates $0 - $6,000 Nil $6,001 - $25,000 15c for each $1 over $6,000 $25,001 - $75,000 $2,850 plus 30c for each $1 over $25,000 $75,001 - $150,000 $17,850 plus 40c for each $1 over $75,000 Over $150,000 $47,850 plus 45c for each $1 over $150,000 Cell B183 is where the user selects their salary range from a drop down box. The formula I have used (which doesn't work) is given below: =IF(...

Please Help: Simple Formula
hey everyone. i need a quick formula for excel. i need to subtract row from row 1 and then divide by row 2. so an example: (B1-B2)/B2 . thi formula works but i need to do it for the whole column. s (Bn-Bn+1)/Bn+1. get it? please help me ou -- Johnny ----------------------------------------------------------------------- Johnny3's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1675 View this thread: http://www.excelforum.com/showthread.php?threadid=31957 See one reply to your later post. Please don't post essentially the same question multiple times. It ta...

formula for interpreting time cards with .25-.50-.75
I have a formula that I used at school =IF(MINUTE(B5)<=5,HOUR(B5),IF(MINUTE(B5)<20,HOUR(B5)+0.25,IF(MINUTE(B5)<35,HOUR(B5)+0.5,IF(MINUTE(B5)<50,HOUR(B5)+0.75,HOUR(B5)+1)))) I was asked to change this to read 53 min before hour 7 minuets after (both showing on time) at 8 min after it goes to a .25 at 21 thru 37 it goes to .50 at 39 thru 52 it goes .75 I hope this is understandable I want to know what numbers to change in the formula thank you Rdewsnap ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messa...

Days Elapsed Formulas
I am looking for a formula that will add the numbers of days between dates. Such as: Survey Ordered Survey Received Days Elapsed 5/2/04 5/25/04 ? 6/5/04 8/5/04 ? Please help!!!! =End_date-Start_date format as general -- For everyone's benefit keep the discussion in the newsgroup. Regards, Peo Sjoblom "krobert@kolterprop.com" <anonymous@discussions.microsoft.com> wrote in message news:124ad01c44283$9745cea0$a501280a@phx.gbl... > I am looking for a formula that will add the numbers of > days between dates. Such as: > ...

excell formula needed
Hi, In Excell, I'm trying to match a cell (eg H21,with a num value (eg 2530) to a column of figures (M34:M99) that has one match in it and produce a grading A, B, C, D from a column (N34:N99). Anyone have any ideas? Rob. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ =Vlookup(H21,$M$34:$N$99,2,false) Bernard "terrificRob" <terrificRob.uixfy@excelforum-nospam.com> wrote in message news:terrificRob.uixfy@excelforum-nospam.com... > Hi, > In Ex...

Nesting an additional formula in an IF statement
I have an existing calculation in a column, when I drag the formula down - I rec'v a #DIV/O - I need to insert a "Serror" in my existing formula but unsure how to complete this? My formula: In M5 =IF((H5/E5)=0,0%,(H5/E5)-1) My formula works great, but I want it to show a blank cell (or 0%) so I can use the sum of M5:M66 in another cell to show an average =AVERAGEA(M5:M66) without rec'v a #DIV/0 (error) Many thanks!! This tweak should suffice In M5: =IF(E5=0,0%,(H5/E5)-1) -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdeme...

Help with what should be a simple formula..
Hi guys! I'm blanking out on how to write this simple formula, and honestly, not sure how to search the forum for help with this particular one. I'm trying to find profit margins basically. I want to break down profits into 1 Hr, 2 Hrs 8 hrs, etc.. for example, I the time it takes to produce item#1...say 10 Minutes.. In the 1 hr box want to figure out the profit based on 60 minutes of work.. My table looks like this.. A1=Item#1; B1=Cost to make Item; C1=Selling Price; D1=Time to make Item; E1=60 Minute profit; E2=120 Minute Profie.etc.tec.etc. A2=Item#2................................

Formula Help Please?
I'm trying to figure out the best formula to resolve the following: A B C Name Jan. Feb 1 Barb 8 3 2 Group 4 5 3 Ernest 6 7 4 Group 7 9 5 Heidi 9 4 6 Group 4 8 7 Total ____ ______ What we are trying to accomplish, is a formula that totals the values in column "B", however, those values which are "group" (B2, B4 & B6) should be multipl...

key board short cut for copying formula from one cell to another
Lets there is a formual in cell lets say "c1"., If I want to copy the formula from c1 to c2, I place the cursor (using mouse) on the bottom right corner fo c1 and drag it to c2. Is there a key board short cut of doing the above. Pls help,., thanks Mario, select C1 and C2, then Ctrl+D. If C1 is the active cell, press Shift+Down Arrow to highlight cells below, then Ctrl+D to copy the formula. To copy to the right rather than down, Ctrl+R. -- DDM "DDM's Microsoft Office Tips and Tricks" Visit us at www.ddmcomputing.com "mario" <anonymous@discuss...

Deleting Rows but not Formulae
I have a worksheet with 200 rows and 50 columns of data with formula in some of the cells. Each month I have to delete some of the rows. I I delete a row the data base moves up and I lose the formulae. How can I delete rows (or the information) without the above problem? Thanks, C -- Message posted from http://www.ExcelForum.com Hi you may post your existing formula -- Regards Frank Kabel Frankfurt, Germany "cefpe >" <<cefpe.1btjub@excelforum-nospam.com> schrieb im Newsbeitrag news:cefpe.1btjub@excelforum-nospam.com... > I have a worksheet with 200 rows and 50 ...

LOCK IN FORMULA
How can I delete data without deleting the formula? You can't delete the "data" (results of the formula) without deleting the formula itself--but you can change the formula to make it look like the cell is empty. (A formula returns something to the cell that contains that formula.) If you want to make it so that it looks empty if certain conditions are met, then you can use a formula like: =if(somecondition="something","",youroriginalformulahere) So if I want to add 7 to A1 but only if A1 is greater than 10, I could use this: =if(a1>10,a1+7,"&...

Formula for adding totals from separate worksheets
I would like to do a summary totals page combining totals from 6 different worksheets. Can anyone assist with a formula for this? Thanks Worked it out - thanks. "TRM" wrote: > I would like to do a summary totals page combining totals from 6 different > worksheets. Can anyone assist with a formula for this? > > Thanks ...

Creating a formula
i have a long list of data with 10 columns of information, one column identifies my committee that's responsible (the committees are identified numerically 1 to 9), I have another column with sub units that roll up to specific committees, sometimes i have one sub unit to one committee, other times i have 4 sub units to 1 committee. how can i create a formula that answers this question: countif[committee column, "1"]and [filed column, "no"] seems simple enough to me but i can't make it work -- Thanks, Susan =SUMPRODUCT(--(Committee_column=1),--(fi...

pivot table adding in a formula
I have a pivot table and i want to add a cumulative total going down the pivot table. example part number | total parts | cumulative total parts to here the cumulative value is a sum of rows abouve current row. anyone have any ideas how i could accomplish this ?? Regards, You can use the Running Total feature. There's an example here: http://www.contextures.com/xlPivot10.html#Running Alexandre wrote: > I have a pivot table and i want to add a cumulative total going down > the pivot table. > > example > > part number | total parts | cumulative total parts...