Sum If Challenge

[Steve Conway]

Here's what I would like to do:

Column A Column B Column C
Revenues $100 Public Safety
Revenues 200 Parks
Expenditures $100 Public Safety
Expenditures $300 General
Expenditures $400 Public Safety

My goal is to write a formula that asks first (starting at
row 1 column A, Is it a an "Expenditure"??...If not go to
the next row, continue till you find an "Expenditure" and
then sum "Public Safety" expenditures only. The answer of
course should be "Public Safety Expenditures" =$500! I was
thinking of the sumif formula, but I can't make it work
with a double question!
thx everyone!
Steve Conway

 

[Paul]

Here's what I would like to do:

Column A Column B Column C
Revenues $100 Public Safety
Revenues 200 Parks
Expenditures $100 Public Safety
Expenditures $300 General
Expenditures $400 Public Safety

My goal is to write a formula that asks first (starting at
row 1 column A, Is it a an "Expenditure"??...If not go to
the next row, continue till you find an "Expenditure" and
then sum "Public Safety" expenditures only. The answer of
course should be "Public Safety Expenditures" =$500! I was
thinking of the sumif formula, but I can't make it work
with a double question!
thx everyone!
Steve Conway

=SUMPRODUCT((A1:A5="Expenditure")*(C1:C5="Public Safety")*B1:B5)

 

[Immanuel]

Try the following array formula:

=SUM(IF((A1:A5="Expenditures")*(C1:C5="Public Safety"),B1:B5,0))

Remember to hit Ctrl-Shift-Enter instead of just Enter after pasting (or
typing) in the formula.

 

[Kevin M]

Steve,

The formula you require is this:
=SUM((A:A="revenues")*(C:C="Public Safety")*B:B)
The formula must be entered as an array. Hit
CTRL+SHIFT+ENTER when finishing the formula, you'll see
{ } around the entire formula. it will diplay 0 if it's
not entered as an array.
HTH
Kevin M

 

[Peo Sjoblom]

One way

=SUMPRODUCT(--(A2:A20="Expenditures"),--(C2:C20="Public Safety"),B2:B20)

better to use

=SUMPRODUCT(--(A2:A20=E2),--(C2:C20=F2),B2:B20)

where you type in the criteria in the 2 cells, that way you don't have to
edit the formula when you
use other criteria

 

[HM]

One way:

sumproduct(("expenditures"=A1:A5)*("Public safety"=C1:C5)*
(B1:B5))

HM

 

[Kevin M]

Peo, can i ask why the -- are used in some formulae? i've
never had a reason to use them before..
Thanks
Kevin M

 

[Peo Sjoblom]

It is supposed to be more effective compared to multiplying the ranges , for
a discussion regarding this see

http://tinyurl.com/v0tz

a side note, your formula won't work, you can't use the whole column like in
A:A with an array formula,
it will return a #NUM error, you have to change it to a set range like
A2:A10000 ..

 

[Guest]

That's right, oops. i copied it out of my cheatsheet i
have for myself. I guess we should also mention that the
ranges must be of identical size for the formula to work
as well. I'll check that discussion out.
Thanks Peo.
Kevin M