SumIf function

N

nc

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

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)
 
D

Duke Carey

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

Bob Phillips

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)
 
D

Domenic

Try...

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

Adjust the range accordingly.

Hope this helps!
 
D

Domenic

Sure...

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

Remove the quotes if your criterion is a numerical value.

Hope this helps!
 
H

Harlan Grove

Domenic wrote...
Sure...

=SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterion")
*(RangeC="Criterion")*(RangeToSum))
Click to expand...
....

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

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

RagDyer

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

nc

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

KL

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
 
D

Domenic

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

Aladin Akyurek

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

Domenic

Most definitely...

Aladin Akyurek said:
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?
Click to expand...
 
Top