SumIf function

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"?
> >
> >
> >

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"?
>

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.

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!.

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"?
> >
> >
> >

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!.
>

Very true...

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

> 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!.
> >

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"?
>
>
>

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.
>>

Actually, the following should suffice...

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

or

=SUMPRODUCT(((A2:A6=F2)+(A2:A6=G2))*(B2:D6))

....where F2 contains your first criterion, such as Dividend, and G2
contains your second criterion, such as Interest.

Hope this helps!

In article <#sYNH#[email protected]>,
"KL" <[email protected]> wrote:

> 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

=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"?

Very true...

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

> 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!.
> >

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.
>>

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.
>

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,

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.
>>

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!.

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!.

Actually, the following should suffice...

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

or

=SUMPRODUCT(((A2:A6=F2)+(A2:A6=G2))*(B2:D6))

....where F2 contains your first criterion, such as Dividend, and G2
contains your second criterion, such as Interest.

Hope this helps!

In article <#sYNH#[email protected]>,
"KL" <[email protected]> wrote:

> 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

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.

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"?
>

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"?
> >
> >
> >

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"?
> >
> >
> >

=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"?
>
>
>

Very true...

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

> 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!.
> >

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.

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.
>

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,

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.
>>

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"?

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"?
> >
> >
> >

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"?
> >
> >
> >

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"?
>
>
>

=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"?

Actually, the following should suffice...

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

or

=SUMPRODUCT(((A2:A6=F2)+(A2:A6=G2))*(B2:D6))

....where F2 contains your first criterion, such as Dividend, and G2
contains your second criterion, such as Interest.

Hope this helps!

In article <#sYNH#[email protected]>,
"KL" <[email protected]> wrote:

> 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

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!.

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.
>>

Most definitely...

In article <[email protected]>,
Aladin Akyurek <[email protected]> wrote:

> 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.
> >>

Actually, the following should suffice...

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

or

=SUMPRODUCT(((A2:A6=F2)+(A2:A6=G2))*(B2:D6))

....where F2 contains your first criterion, such as Dividend, and G2
contains your second criterion, such as Interest.

Hope this helps!

In article <#sYNH#[email protected]>,
"KL" <[email protected]> wrote:

> 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

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.
>

Very true...

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

> 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!.
>

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!.

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.
>>

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!.

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.
>>

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"?
>

Most definitely...

In article <[email protected]>,
Aladin Akyurek <[email protected]> wrote:

> 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.
> >>

=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"?
> >
> >
> >

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"?

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.
>>

Actually, the following should suffice...

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

or

=SUMPRODUCT(((A2:A6=F2)+(A2:A6=G2))*(B2:D6))

....where F2 contains your first criterion, such as Dividend, and G2
contains your second criterion, such as Interest.

Hope this helps!

In article <#sYNH#[email protected]>,
"KL" <[email protected]> wrote:

> 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

Most definitely...

In article <[email protected]>,
Aladin Akyurek <[email protected]> wrote:

> 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.
> >>

Very true...

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

> 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.
>

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"?

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"?
> >
> >
> >

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"?
> >
> >
> >

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"?
>
>
>

=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"?

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.

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!.
>

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.
>>

Very true...

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

> 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!.
> >

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"?
>

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.
>

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!.

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.
>>

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.

Actually, the following should suffice...

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

or

=SUMPRODUCT(((A2:A6=F2)+(A2:A6=G2))*(B2:D6))

....where F2 contains your first criterion, such as Dividend, and G2
contains your second criterion, such as Interest.

Hope this helps!

In article <#sYNH#[email protected]>,
"KL" <[email protected]> wrote:

> 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

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!.

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.
>>

Most definitely...

In article <[email protected]>,
Aladin Akyurek <[email protected]> wrote:

> 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.
> >>