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="Inco me"),C2:C20)

--

HTH

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


"nc" wrote in message
...
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="Inco me"),C2:C20)

--

HTH

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


"nc" wrote in message
...
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="Inco me"),C2:C20)

--

HTH

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


"nc" wrote in message
...
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" wrote in message
...
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="Inco me"),C2:C20)

--

HTH

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


"nc" wrote in message
...
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 ,
"nc" 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 ,
"nc" 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="Criterio n")*(RangeC="Criterion"
)*(RangeToSum))

Remove the quotes if your criterion is a numerical value.

Hope this helps!

In article ,
"nc" wrote:

Thanks Domenic.

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

Domenic wrote...
Sure...

=SUMPRODUCT((RangeA="Criterion")*(RangeB="Criteri on")
*(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="Criterio n")
*(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 . com,
"Harlan Grove" wrote:

Domenic wrote...
Sure...

=SUMPRODUCT((RangeA="Criterion")*(RangeB="Criteri on")
*(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="Criterio n")
*(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" wrote in message
ups.com...
Domenic wrote...
Sure...

=SUMPRODUCT((RangeA="Criterion")*(RangeB="Criteri on")
*(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="Criterio n")
*(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 ,
"RagDyer" 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" wrote in message
ups.com...
Domenic wrote...
Sure...

=SUMPRODUCT((RangeA="Criterion")*(RangeB="Criteri on")
*(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="Criterio n")
*(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="Criterio n")*(RangeC="Criterion"
)*(RangeToSum))

Remove the quotes if your criterion is a numerical value.

Hope this helps!

In article ,
"nc" 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" wrote in message
...
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="Criterio n")*(RangeC="Criterion"
)*(RangeToSum))

Remove the quotes if your criterion is a numerical value.

Hope this helps!

In article ,
"nc" 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 ,
"KL" 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

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="Criter ion")*(RangeC="Criterion"
)*(RangeToSum))

Remove the quotes if your criterion is a numerical value.

Hope this helps!

In article ,
"nc" wrote:


Thanks Domenic.

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

Most definitely...

In article ,
Aladin Akyurek 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="Criter ion")*(RangeC="Criterion"
)*(RangeToSum))

Remove the quotes if your criterion is a numerical value.

Hope this helps!

In article ,
"nc" wrote:


Thanks Domenic.

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