Nesting SUMIF formulas

[Todd]

Can I nest a SUMIF within a SUMIF formula?

[chalky]

Yes. Upto 7 i believe but how are you planning on doing it?

[Peo Sjoblom]

No you can't. Post the problem and you'll probably will receive a solution
though

--

Regards,

Peo Sjoblom

"Todd" <[email protected]> wrote in message
news:[email protected]...
> Can I nest a SUMIF within a SUMIF formula?

[Todd]

I have a file set up in database fashion that I am trying to find the easiest
way to summarize data using more than one criteria. I am also looking into
using tables. This database gets added to and resorted frequently so I did
not want to use Subtotals.

For instance a database of automobiles that includes Make, Model and several
other distinguishing fields. I was trying to use a SUMIF formula to give me
the total of all the Ford Explorers vs. Jeep Cherokees vs. Dodge Rams etc.
I could not figure out how to actually write a nested SUMIF formula on both
the Make and Model fields.

I am also looking into using tables.

Todd

"chalky" wrote:

>
> Yes. Upto 7 i believe but how are you planning on doing it?
>
>
> --
> chalky
> ------------------------------------------------------------------------
> chalky's Profile: http://www.excelforum.com/member.php...o&userid=23758
> View this thread: http://www.excelforum.com/showthread...hreadid=484302
>
>

[Peo Sjoblom]

Use

=SUMPRODUCT(--(Make_Range="Ford"),--(Model_Range="Explorer"),Sum_Range)

replace the hardcoded Ford and Explorer with cells like B2 and C2 in where
you would type the make and model thus avoiding to having to edit the
formula when you change make/model

Ranges need to be of the same size and cannot be the whole column like
A:A as opposed to A2:A100


--

Regards,

Peo Sjoblom

"Todd" <[email protected]> wrote in message
news:[email protected]...
> I have a file set up in database fashion that I am trying to find the
easiest
> way to summarize data using more than one criteria. I am also looking
into
> using tables. This database gets added to and resorted frequently so I
did
> not want to use Subtotals.
>
> For instance a database of automobiles that includes Make, Model and
several
> other distinguishing fields. I was trying to use a SUMIF formula to give
me
> the total of all the Ford Explorers vs. Jeep Cherokees vs. Dodge Rams etc.
> I could not figure out how to actually write a nested SUMIF formula on
both
> the Make and Model fields.
>
> I am also looking into using tables.
>
> Todd
>
> "chalky" wrote:
>
> >
> > Yes. Upto 7 i believe but how are you planning on doing it?
> >
> >
> > --
> > chalky
> > ------------------------------------------------------------------------
> > chalky's Profile:
http://www.excelforum.com/member.php...o&userid=23758
> > View this thread:
http://www.excelforum.com/showthread...hreadid=484302
> >
> >

[Phil]

The formula you have posted resolves a problem I have been having.

However, I cannot see why it works (as SUMPRODUCT multipllies corresponding
components in the given arrays and sums the answer). How can arrays that
don't have values be multiplied. Are the minus signs significant here. I
notice that if you remove them the formula doesnt work

"Peo Sjoblom" wrote:

> Use
>
> =SUMPRODUCT(--(Make_Range="Ford"),--(Model_Range="Explorer"),Sum_Range)
>
> replace the hardcoded Ford and Explorer with cells like B2 and C2 in where
> you would type the make and model thus avoiding to having to edit the
> formula when you change make/model
>
> Ranges need to be of the same size and cannot be the whole column like
> A:A as opposed to A2:A100
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> "Todd" <[email protected]> wrote in message
> news:[email protected]...
> > I have a file set up in database fashion that I am trying to find the
> easiest
> > way to summarize data using more than one criteria. I am also looking
> into
> > using tables. This database gets added to and resorted frequently so I
> did
> > not want to use Subtotals.
> >
> > For instance a database of automobiles that includes Make, Model and
> several
> > other distinguishing fields. I was trying to use a SUMIF formula to give
> me
> > the total of all the Ford Explorers vs. Jeep Cherokees vs. Dodge Rams etc.
> > I could not figure out how to actually write a nested SUMIF formula on
> both
> > the Make and Model fields.
> >
> > I am also looking into using tables.
> >
> > Todd
> >
> > "chalky" wrote:
> >
> > >
> > > Yes. Upto 7 i believe but how are you planning on doing it?
> > >
> > >
> > > --
> > > chalky
> > > ------------------------------------------------------------------------
> > > chalky's Profile:
> http://www.excelforum.com/member.php...o&userid=23758
> > > View this thread:
> http://www.excelforum.com/showthread...hreadid=484302
> > >
> > >
>
>
>

[Roger Govier]

Hi

The tests will return TRUE or FALSE.
Multiplying by the double unary (--) coerces these results to 1 and 0
respectively which are then used within the Sumproduct array
calculation.

--
Regards

Roger Govier


"Phil" <[email protected]> wrote in message
news:[email protected]...
> The formula you have posted resolves a problem I have been having.
>
> However, I cannot see why it works (as SUMPRODUCT multipllies
> corresponding
> components in the given arrays and sums the answer). How can arrays
> that
> don't have values be multiplied. Are the minus signs significant
> here. I
> notice that if you remove them the formula doesnt work
>
> "Peo Sjoblom" wrote:
>
>> Use
>>
>> =SUMPRODUCT(--(Make_Range="Ford"),--(Model_Range="Explorer"),Sum_Range)
>>
>> replace the hardcoded Ford and Explorer with cells like B2 and C2 in
>> where
>> you would type the make and model thus avoiding to having to edit the
>> formula when you change make/model
>>
>> Ranges need to be of the same size and cannot be the whole column
>> like
>> A:A as opposed to A2:A100
>>
>>
>> --
>>
>> Regards,
>>
>> Peo Sjoblom
>>
>> "Todd" <[email protected]> wrote in message
>> news:[email protected]...
>> > I have a file set up in database fashion that I am trying to find
>> > the
>> easiest
>> > way to summarize data using more than one criteria. I am also
>> > looking
>> into
>> > using tables. This database gets added to and resorted frequently
>> > so I
>> did
>> > not want to use Subtotals.
>> >
>> > For instance a database of automobiles that includes Make, Model
>> > and
>> several
>> > other distinguishing fields. I was trying to use a SUMIF formula
>> > to give
>> me
>> > the total of all the Ford Explorers vs. Jeep Cherokees vs. Dodge
>> > Rams etc.
>> > I could not figure out how to actually write a nested SUMIF formula
>> > on
>> both
>> > the Make and Model fields.
>> >
>> > I am also looking into using tables.
>> >
>> > Todd
>> >
>> > "chalky" wrote:
>> >
>> > >
>> > > Yes. Upto 7 i believe but how are you planning on doing it?
>> > >
>> > >
>> > > --
>> > > chalky
>> > > ------------------------------------------------------------------------
>> > > chalky's Profile:
>> http://www.excelforum.com/member.php...o&userid=23758
>> > > View this thread:
>> http://www.excelforum.com/showthread...hreadid=484302
>> > >
>> > >
>>
>>
>>