SumIf function

[Guest]

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

 

[Bob Phillips]

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

--

HTH

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

 

[Guest]

Thanks Bob.

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

 

[Guest]

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

 

[Bob Phillips]

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

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

--

HTH

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

 

[Domenic]

Try...

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

Adjust the range accordingly.

Hope this helps!

 

[Guest]

Thanks Domenic.

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

 

[Domenic]

Sure...

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

Remove the quotes if your criterion is a numerical value.

Hope this helps!

 

[Harlan Grove]

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]

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!

 

[RagDyer]

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

 

[Domenic]

Very true...

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

 

[Guest]

Hi Domenic

I tried using the following function,

=SUMPRODUCT((A2:A6="Dividend")*(A2:A6="Interest")*(B26)) 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.

 

[KL]

Hi,

Try this:

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

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

 

[Aladin Akyurek]

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

E2, copied down:

=SUM(B22)

which allows you to avoid expensive conditional calculations?

 

[Domenic]

Most definitely...

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

E2, copied down:

=SUM(B22)

which allows you to avoid expensive conditional calculations?