Sum If Challenge

  • Thread starter Thread starter Steve Conway
  • Start date Start date
S

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
 
Steve Conway said:
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
Click to expand...

=SUMPRODUCT((A1:A5="Expenditure")*(C1:C5="Public Safety")*B1:B5)
 
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.
 
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
 
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
 
Peo, can i ask why the -- are used in some formulae? i've
never had a reason to use them before..
Thanks
Kevin M
 
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 ..
 
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
 
Back
Top