SumIf function

EENF EPDA EKHC GGEN
Expenditure 1 2 3 4
Income 2 2 2 6
Capital additions 5 2 4 5
Income 7 8 9 5
Capital additions 10 11 12 11

How can I total the more than one column if the criteria is "Income"?

=sumif(A2:A20="Income"),B2:B20)+sumif(A2:A20="Income"),C2:C20)

--

HTH

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


"nc" <[email protected]> wrote in message
news:[email protected]...
> EENF EPDA EKHC GGEN
> Expenditure 1 2 3 4
> Income 2 2 2 6
> Capital additions 5 2 4 5
> Income 7 8 9 5
> Capital additions 10 11 12 11
>
> How can I total the more than one column if the criteria is "Income"?

=sumif(A2:A20="Income"),B2:B20)+sumif(A2:A20="Income"),C2:C20)

--

HTH

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


"nc" <[email protected]> wrote in message
news:[email protected]...
> EENF EPDA EKHC GGEN
> Expenditure 1 2 3 4
> Income 2 2 2 6
> Capital additions 5 2 4 5
> Income 7 8 9 5
> Capital additions 10 11 12 11
>
> How can I total the more than one column if the criteria is "Income"?

=sumif(A2:A20="Income"),B2:B20)+sumif(A2:A20="Income"),C2:C20)

--

HTH

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


"nc" <[email protected]> wrote in message
news:[email protected]...
> EENF EPDA EKHC GGEN
> Expenditure 1 2 3 4
> Income 2 2 2 6
> Capital additions 5 2 4 5
> Income 7 8 9 5
> Capital additions 10 11 12 11
>
> How can I total the more than one column if the criteria is "Income"?

Thanks Bob.

I have about 10 columns. I was looking for a symplified function.

"Bob Phillips" wrote:

> =sumif(A2:A20="Income"),B2:B20)+sumif(A2:A20="Income"),C2:C20)
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "nc" <[email protected]> wrote in message
> news:[email protected]...
> > EENF EPDA EKHC GGEN
> > Expenditure 1 2 3 4
> > Income 2 2 2 6
> > Capital additions 5 2 4 5
> > Income 7 8 9 5
> > Capital additions 10 11 12 11
> >
> > How can I total the more than one column if the criteria is "Income"?
>
>
>

Thanks Bob.

I have about 10 columns. I was looking for a symplified function.

"Bob Phillips" wrote:

> =sumif(A2:A20="Income"),B2:B20)+sumif(A2:A20="Income"),C2:C20)
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "nc" <[email protected]> wrote in message
> news:[email protected]...
> > EENF EPDA EKHC GGEN
> > Expenditure 1 2 3 4
> > Income 2 2 2 6
> > Capital additions 5 2 4 5
> > Income 7 8 9 5
> > Capital additions 10 11 12 11
> >
> > How can I total the more than one column if the criteria is "Income"?
>
>
>

Thanks Bob.

I have about 10 columns. I was looking for a symplified function.

"Bob Phillips" wrote:

> =sumif(A2:A20="Income"),B2:B20)+sumif(A2:A20="Income"),C2:C20)
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "nc" <[email protected]> wrote in message
> news:[email protected]...
> > EENF EPDA EKHC GGEN
> > Expenditure 1 2 3 4
> > Income 2 2 2 6
> > Capital additions 5 2 4 5
> > Income 7 8 9 5
> > Capital additions 10 11 12 11
> >
> > How can I total the more than one column if the criteria is "Income"?
>
>
>

Then add one more column that sums across the row, then use one of Bob's
SUMIF() functions and reference the new column

"nc" wrote:

> Thanks Bob.
>
> I have about 10 columns. I was looking for a symplified function.
>
> "Bob Phillips" wrote:
>
> > =sumif(A2:A20="Income"),B2:B20)+sumif(A2:A20="Income"),C2:C20)
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "nc" <[email protected]> wrote in message
> > news:[email protected]...
> > > EENF EPDA EKHC GGEN
> > > Expenditure 1 2 3 4
> > > Income 2 2 2 6
> > > Capital additions 5 2 4 5
> > > Income 7 8 9 5
> > > Capital additions 10 11 12 11
> > >
> > > How can I total the more than one column if the criteria is "Income"?
> >
> >
> >

Then add one more column that sums across the row, then use one of Bob's
SUMIF() functions and reference the new column

"nc" wrote:

> Thanks Bob.
>
> I have about 10 columns. I was looking for a symplified function.
>
> "Bob Phillips" wrote:
>
> > =sumif(A2:A20="Income"),B2:B20)+sumif(A2:A20="Income"),C2:C20)
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "nc" <[email protected]> wrote in message
> > news:[email protected]...
> > > EENF EPDA EKHC GGEN
> > > Expenditure 1 2 3 4
> > > Income 2 2 2 6
> > > Capital additions 5 2 4 5
> > > Income 7 8 9 5
> > > Capital additions 10 11 12 11
> > >
> > > How can I total the more than one column if the criteria is "Income"?
> >
> >
> >

Then add one more column that sums across the row, then use one of Bob's
SUMIF() functions and reference the new column

"nc" wrote:

> Thanks Bob.
>
> I have about 10 columns. I was looking for a symplified function.
>
> "Bob Phillips" wrote:
>
> > =sumif(A2:A20="Income"),B2:B20)+sumif(A2:A20="Income"),C2:C20)
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "nc" <[email protected]> wrote in message
> > news:[email protected]...
> > > EENF EPDA EKHC GGEN
> > > Expenditure 1 2 3 4
> > > Income 2 2 2 6
> > > Capital additions 5 2 4 5
> > > Income 7 8 9 5
> > > Capital additions 10 11 12 11
> > >
> > > How can I total the more than one column if the criteria is "Income"?
> >
> >
> >

Here is a formula with a few columns, add the rest

=SUMPRODUCT(--(A2:A20="Income"),B2:B20+C2:C20+D2:D20)

--

HTH

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


"nc" <[email protected]> wrote in message
news:[email protected]...
> Thanks Bob.
>
> I have about 10 columns. I was looking for a symplified function.
>
> "Bob Phillips" wrote:
>
> > =sumif(A2:A20="Income"),B2:B20)+sumif(A2:A20="Income"),C2:C20)
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "nc" <[email protected]> wrote in message
> > news:[email protected]...
> > > EENF EPDA EKHC GGEN
> > > Expenditure 1 2 3 4
> > > Income 2 2 2 6
> > > Capital additions 5 2 4 5
> > > Income 7 8 9 5
> > > Capital additions 10 11 12 11
> > >
> > > How can I total the more than one column if the criteria is "Income"?
> >
> >
> >

Here is a formula with a few columns, add the rest

=SUMPRODUCT(--(A2:A20="Income"),B2:B20+C2:C20+D2:D20)

--

HTH

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


"nc" <[email protected]> wrote in message
news:[email protected]...
> Thanks Bob.
>
> I have about 10 columns. I was looking for a symplified function.
>
> "Bob Phillips" wrote:
>
> > =sumif(A2:A20="Income"),B2:B20)+sumif(A2:A20="Income"),C2:C20)
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "nc" <[email protected]> wrote in message
> > news:[email protected]...
> > > EENF EPDA EKHC GGEN
> > > Expenditure 1 2 3 4
> > > Income 2 2 2 6
> > > Capital additions 5 2 4 5
> > > Income 7 8 9 5
> > > Capital additions 10 11 12 11
> > >
> > > How can I total the more than one column if the criteria is "Income"?
> >
> >
> >

Here is a formula with a few columns, add the rest

=SUMPRODUCT(--(A2:A20="Income"),B2:B20+C2:C20+D2:D20)

--

HTH

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


"nc" <[email protected]> wrote in message
news:[email protected]...
> Thanks Bob.
>
> I have about 10 columns. I was looking for a symplified function.
>
> "Bob Phillips" wrote:
>
> > =sumif(A2:A20="Income"),B2:B20)+sumif(A2:A20="Income"),C2:C20)
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "nc" <[email protected]> wrote in message
> > news:[email protected]...
> > > EENF EPDA EKHC GGEN
> > > Expenditure 1 2 3 4
> > > Income 2 2 2 6
> > > Capital additions 5 2 4 5
> > > Income 7 8 9 5
> > > Capital additions 10 11 12 11
> > >
> > > How can I total the more than one column if the criteria is "Income"?
> >
> >
> >

Try...

=SUMPRODUCT((A2:A6="Income")*(B2:E6))

Adjust the range accordingly.

Hope this helps!

In article <[email protected]>,
"nc" <[email protected]> wrote:

> EENF EPDA EKHC GGEN
> Expenditure 1 2 3 4
> Income 2 2 2 6
> Capital additions 5 2 4 5
> Income 7 8 9 5
> Capital additions 10 11 12 11
>
> How can I total the more than one column if the criteria is "Income"?

Try...

=SUMPRODUCT((A2:A6="Income")*(B2:E6))

Adjust the range accordingly.

Hope this helps!

In article <[email protected]>,
"nc" <[email protected]> wrote:

> EENF EPDA EKHC GGEN
> Expenditure 1 2 3 4
> Income 2 2 2 6
> Capital additions 5 2 4 5
> Income 7 8 9 5
> Capital additions 10 11 12 11
>
> How can I total the more than one column if the criteria is "Income"?

Try...

=SUMPRODUCT((A2:A6="Income")*(B2:E6))

Adjust the range accordingly.

Hope this helps!

In article <[email protected]>,
"nc" <[email protected]> wrote:

> EENF EPDA EKHC GGEN
> Expenditure 1 2 3 4
> Income 2 2 2 6
> Capital additions 5 2 4 5
> Income 7 8 9 5
> Capital additions 10 11 12 11
>
> How can I total the more than one column if the criteria is "Income"?

Thanks Domenic.

Can this function be adapted to use more than one criteria.

"Domenic" wrote:

> Try...
>
> =SUMPRODUCT((A2:A6="Income")*(B2:E6))
>
> Adjust the range accordingly.
>
> Hope this helps!
>
> In article <[email protected]>,
> "nc" <[email protected]> wrote:
>
> > EENF EPDA EKHC GGEN
> > Expenditure 1 2 3 4
> > Income 2 2 2 6
> > Capital additions 5 2 4 5
> > Income 7 8 9 5
> > Capital additions 10 11 12 11
> >
> > How can I total the more than one column if the criteria is "Income"?
>

Thanks Domenic.

Can this function be adapted to use more than one criteria.

"Domenic" wrote:

> Try...
>
> =SUMPRODUCT((A2:A6="Income")*(B2:E6))
>
> Adjust the range accordingly.
>
> Hope this helps!
>
> In article <[email protected]>,
> "nc" <[email protected]> wrote:
>
> > EENF EPDA EKHC GGEN
> > Expenditure 1 2 3 4
> > Income 2 2 2 6
> > Capital additions 5 2 4 5
> > Income 7 8 9 5
> > Capital additions 10 11 12 11
> >
> > How can I total the more than one column if the criteria is "Income"?
>

Thanks Domenic.

Can this function be adapted to use more than one criteria.

"Domenic" wrote:

> Try...
>
> =SUMPRODUCT((A2:A6="Income")*(B2:E6))
>
> Adjust the range accordingly.
>
> Hope this helps!
>
> In article <[email protected]>,
> "nc" <[email protected]> wrote:
>
> > EENF EPDA EKHC GGEN
> > Expenditure 1 2 3 4
> > Income 2 2 2 6
> > Capital additions 5 2 4 5
> > Income 7 8 9 5
> > Capital additions 10 11 12 11
> >
> > How can I total the more than one column if the criteria is "Income"?
>

Sure...

=SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterion")*(RangeC="Criterion"
)*(RangeToSum))

Remove the quotes if your criterion is a numerical value.

Hope this helps!

In article <[email protected]>,
"nc" <[email protected]> wrote:

> Thanks Domenic.
>
> Can this function be adapted to use more than one criteria.

Sure...

=SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterion")*(RangeC="Criterion"
)*(RangeToSum))

Remove the quotes if your criterion is a numerical value.

Hope this helps!

In article <[email protected]>,
"nc" <[email protected]> wrote:

> Thanks Domenic.
>
> Can this function be adapted to use more than one criteria.

Domenic wrote...
>Sure...
>
>=SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterion")
>*(RangeC="Criterion")*(RangeToSum))
....

I'm not an absolutist about using separate arguments for all criteria
arguments, but the value array should be a separate argument in
conditional sums (as opposed to conditional counts), i.e.,

=SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterion")
*(RangeC="Criterion"),RangeToSum)

because SUMPRODUCT will ignore entries in RangeToSum that aren't
numeric and can't be converted to numeric *IF* RangeToSum were a
separate argument. In that situation, your formula would return
#VALUE!.

Domenic wrote...
>Sure...
>
>=SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterion")
>*(RangeC="Criterion")*(RangeToSum))
....

I'm not an absolutist about using separate arguments for all criteria
arguments, but the value array should be a separate argument in
conditional sums (as opposed to conditional counts), i.e.,

=SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterion")
*(RangeC="Criterion"),RangeToSum)

because SUMPRODUCT will ignore entries in RangeToSum that aren't
numeric and can't be converted to numeric *IF* RangeToSum were a
separate argument. In that situation, your formula would return
#VALUE!.

Domenic wrote...
>Sure...
>
>=SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterion")
>*(RangeC="Criterion")*(RangeToSum))
....

I'm not an absolutist about using separate arguments for all criteria
arguments, but the value array should be a separate argument in
conditional sums (as opposed to conditional counts), i.e.,

=SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterion")
*(RangeC="Criterion"),RangeToSum)

because SUMPRODUCT will ignore entries in RangeToSum that aren't
numeric and can't be converted to numeric *IF* RangeToSum were a
separate argument. In that situation, your formula would return
#VALUE!.

Thanks Harlan! The reason I didn't use a separate argument here is that
the range actually spans a number of columns. I should have made that
clear...

Thanks again, Harlan!

In article <[email protected]>,
"Harlan Grove" <[email protected]> wrote:

> Domenic wrote...
> >Sure...
> >
> >=SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterion")
> >*(RangeC="Criterion")*(RangeToSum))
> ...
>
> I'm not an absolutist about using separate arguments for all criteria
> arguments, but the value array should be a separate argument in
> conditional sums (as opposed to conditional counts), i.e.,
>
> =SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterion")
> *(RangeC="Criterion"),RangeToSum)
>
> because SUMPRODUCT will ignore entries in RangeToSum that aren't
> numeric and can't be converted to numeric *IF* RangeToSum were a
> separate argument. In that situation, your formula would return
> #VALUE!.

Thanks Harlan! The reason I didn't use a separate argument here is that
the range actually spans a number of columns. I should have made that
clear...

Thanks again, Harlan!

In article <[email protected]>,
"Harlan Grove" <[email protected]> wrote:

> Domenic wrote...
> >Sure...
> >
> >=SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterion")
> >*(RangeC="Criterion")*(RangeToSum))
> ...
>
> I'm not an absolutist about using separate arguments for all criteria
> arguments, but the value array should be a separate argument in
> conditional sums (as opposed to conditional counts), i.e.,
>
> =SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterion")
> *(RangeC="Criterion"),RangeToSum)
>
> because SUMPRODUCT will ignore entries in RangeToSum that aren't
> numeric and can't be converted to numeric *IF* RangeToSum were a
> separate argument. In that situation, your formula would return
> #VALUE!.

Thanks Harlan! The reason I didn't use a separate argument here is that
the range actually spans a number of columns. I should have made that
clear...

Thanks again, Harlan!

In article <[email protected]>,
"Harlan Grove" <[email protected]> wrote:

> Domenic wrote...
> >Sure...
> >
> >=SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterion")
> >*(RangeC="Criterion")*(RangeToSum))
> ...
>
> I'm not an absolutist about using separate arguments for all criteria
> arguments, but the value array should be a separate argument in
> conditional sums (as opposed to conditional counts), i.e.,
>
> =SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterion")
> *(RangeC="Criterion"),RangeToSum)
>
> because SUMPRODUCT will ignore entries in RangeToSum that aren't
> numeric and can't be converted to numeric *IF* RangeToSum were a
> separate argument. In that situation, your formula would return
> #VALUE!.

But in some (many) situations, that error return would be a welcomed trap,
denoting contaminated data.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> Domenic wrote...
> >Sure...
> >
> >=SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterion")
> >*(RangeC="Criterion")*(RangeToSum))
> ...
>
> I'm not an absolutist about using separate arguments for all criteria
> arguments, but the value array should be a separate argument in
> conditional sums (as opposed to conditional counts), i.e.,
>
> =SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterion")
> *(RangeC="Criterion"),RangeToSum)
>
> because SUMPRODUCT will ignore entries in RangeToSum that aren't
> numeric and can't be converted to numeric *IF* RangeToSum were a
> separate argument. In that situation, your formula would return
> #VALUE!.
>

But in some (many) situations, that error return would be a welcomed trap,
denoting contaminated data.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> Domenic wrote...
> >Sure...
> >
> >=SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterion")
> >*(RangeC="Criterion")*(RangeToSum))
> ...
>
> I'm not an absolutist about using separate arguments for all criteria
> arguments, but the value array should be a separate argument in
> conditional sums (as opposed to conditional counts), i.e.,
>
> =SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterion")
> *(RangeC="Criterion"),RangeToSum)
>
> because SUMPRODUCT will ignore entries in RangeToSum that aren't
> numeric and can't be converted to numeric *IF* RangeToSum were a
> separate argument. In that situation, your formula would return
> #VALUE!.
>

But in some (many) situations, that error return would be a welcomed trap,
denoting contaminated data.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> Domenic wrote...
> >Sure...
> >
> >=SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterion")
> >*(RangeC="Criterion")*(RangeToSum))
> ...
>
> I'm not an absolutist about using separate arguments for all criteria
> arguments, but the value array should be a separate argument in
> conditional sums (as opposed to conditional counts), i.e.,
>
> =SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterion")
> *(RangeC="Criterion"),RangeToSum)
>
> because SUMPRODUCT will ignore entries in RangeToSum that aren't
> numeric and can't be converted to numeric *IF* RangeToSum were a
> separate argument. In that situation, your formula would return
> #VALUE!.
>

Hi Domenic

I tried using the following function,

=SUMPRODUCT((A2:A6="Dividend")*(A2:A6="Interest")*(B2:D6)) with the table
below and I get a value of zero. I was expecting 1200.

Jan Feb March
Dividend 100 100 100
Interest 100 100 100
Expenses -100 -100 -100
Dividend 100 100 100
Interest 100 100 100


Thanks.


"Domenic" wrote:

> Sure...
>
> =SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterion")*(RangeC="Criterion"
> )*(RangeToSum))
>
> Remove the quotes if your criterion is a numerical value.
>
> Hope this helps!
>
> In article <[email protected]>,
> "nc" <[email protected]> wrote:
>
> > Thanks Domenic.
> >
> > Can this function be adapted to use more than one criteria.
>

Hi Domenic

I tried using the following function,

=SUMPRODUCT((A2:A6="Dividend")*(A2:A6="Interest")*(B2:D6)) with the table
below and I get a value of zero. I was expecting 1200.

Jan Feb March
Dividend 100 100 100
Interest 100 100 100
Expenses -100 -100 -100
Dividend 100 100 100
Interest 100 100 100


Thanks.


"Domenic" wrote:

> Sure...
>
> =SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterion")*(RangeC="Criterion"
> )*(RangeToSum))
>
> Remove the quotes if your criterion is a numerical value.
>
> Hope this helps!
>
> In article <[email protected]>,
> "nc" <[email protected]> wrote:
>
> > Thanks Domenic.
> >
> > Can this function be adapted to use more than one criteria.
>

Hi Domenic

I tried using the following function,

=SUMPRODUCT((A2:A6="Dividend")*(A2:A6="Interest")*(B2:D6)) with the table
below and I get a value of zero. I was expecting 1200.

Jan Feb March
Dividend 100 100 100
Interest 100 100 100
Expenses -100 -100 -100
Dividend 100 100 100
Interest 100 100 100


Thanks.


"Domenic" wrote:

> Sure...
>
> =SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterion")*(RangeC="Criterion"
> )*(RangeToSum))
>
> Remove the quotes if your criterion is a numerical value.
>
> Hope this helps!
>
> In article <[email protected]>,
> "nc" <[email protected]> wrote:
>
> > Thanks Domenic.
> >
> > Can this function be adapted to use more than one criteria.
>

Hi,

Try this:

=SUMPRODUCT(((A2:A6="Dividend")+(A2:A6="Interest")>0)*(B2:D6))

Your formula basically says sum if a cell is equal to "Dividend" AND
"Interest" (which isn't possible) while it should say sum if a cell is equal
to "Dividend" OR "Interest"

Regards,
KL


"nc" <[email protected]> wrote in message
news:[email protected]...
> Hi Domenic
>
> I tried using the following function,
>
> =SUMPRODUCT((A2:A6="Dividend")*(A2:A6="Interest")*(B2:D6)) with the table
> below and I get a value of zero. I was expecting 1200.
>
> Jan Feb March
> Dividend 100 100 100
> Interest 100 100 100
> Expenses -100 -100 -100
> Dividend 100 100 100
> Interest 100 100 100
>
>
> Thanks.
>
>
> "Domenic" wrote:
>
>> Sure...
>>
>> =SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterion")*(RangeC="Criterion"
>> )*(RangeToSum))
>>
>> Remove the quotes if your criterion is a numerical value.
>>
>> Hope this helps!
>>
>> In article <[email protected]>,
>> "nc" <[email protected]> wrote:
>>
>> > Thanks Domenic.
>> >
>> > Can this function be adapted to use more than one criteria.
>>

Hi,

Try this:

=SUMPRODUCT(((A2:A6="Dividend")+(A2:A6="Interest")>0)*(B2:D6))

Your formula basically says sum if a cell is equal to "Dividend" AND
"Interest" (which isn't possible) while it should say sum if a cell is equal
to "Dividend" OR "Interest"

Regards,
KL


"nc" <[email protected]> wrote in message
news:[email protected]...
> Hi Domenic
>
> I tried using the following function,
>
> =SUMPRODUCT((A2:A6="Dividend")*(A2:A6="Interest")*(B2:D6)) with the table
> below and I get a value of zero. I was expecting 1200.
>
> Jan Feb March
> Dividend 100 100 100
> Interest 100 100 100
> Expenses -100 -100 -100
> Dividend 100 100 100
> Interest 100 100 100
>
>
> Thanks.
>
>
> "Domenic" wrote:
>
>> Sure...
>>
>> =SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterion")*(RangeC="Criterion"
>> )*(RangeToSum))
>>
>> Remove the quotes if your criterion is a numerical value.
>>
>> Hope this helps!
>>
>> In article <[email protected]>,
>> "nc" <[email protected]> wrote:
>>
>> > Thanks Domenic.
>> >
>> > Can this function be adapted to use more than one criteria.
>>

Hi,

Try this:

=SUMPRODUCT(((A2:A6="Dividend")+(A2:A6="Interest")>0)*(B2:D6))

Your formula basically says sum if a cell is equal to "Dividend" AND
"Interest" (which isn't possible) while it should say sum if a cell is equal
to "Dividend" OR "Interest"

Regards,
KL


"nc" <[email protected]> wrote in message
news:[email protected]...
> Hi Domenic
>
> I tried using the following function,
>
> =SUMPRODUCT((A2:A6="Dividend")*(A2:A6="Interest")*(B2:D6)) with the table
> below and I get a value of zero. I was expecting 1200.
>
> Jan Feb March
> Dividend 100 100 100
> Interest 100 100 100
> Expenses -100 -100 -100
> Dividend 100 100 100
> Interest 100 100 100
>
>
> Thanks.
>
>
> "Domenic" wrote:
>
>> Sure...
>>
>> =SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterion")*(RangeC="Criterion"
>> )*(RangeToSum))
>>
>> Remove the quotes if your criterion is a numerical value.
>>
>> Hope this helps!
>>
>> In article <[email protected]>,
>> "nc" <[email protected]> wrote:
>>
>> > Thanks Domenic.
>> >
>> > Can this function be adapted to use more than one criteria.
>>

Why not create an additional column, say E, that sums monthly values
like in:

E2, copied down:

=SUM(B2:D2)

which allows you to avoid expensive conditional calculations?

nc wrote:
> Hi Domenic
>
> I tried using the following function,
>
> =SUMPRODUCT((A2:A6="Dividend")*(A2:A6="Interest")*(B2:D6)) with the table
> below and I get a value of zero. I was expecting 1200.
>
> Jan Feb March
> Dividend 100 100 100
> Interest 100 100 100
> Expenses -100 -100 -100
> Dividend 100 100 100
> Interest 100 100 100
>
>
> Thanks.
>
>
> "Domenic" wrote:
>
>
>>Sure...
>>
>>=SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterion")*(RangeC="Criterion"
>>)*(RangeToSum))
>>
>>Remove the quotes if your criterion is a numerical value.
>>
>>Hope this helps!
>>
>>In article <[email protected]>,
>> "nc" <[email protected]> wrote:
>>
>>
>>>Thanks Domenic.
>>>
>>>Can this function be adapted to use more than one criteria.
>>

Sure...

=SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterion")*(RangeC="Criterion"
)*(RangeToSum))

Remove the quotes if your criterion is a numerical value.

Hope this helps!

In article <[email protected]>,
"nc" <[email protected]> wrote:

> Thanks Domenic.
>
> Can this function be adapted to use more than one criteria.