Sumif want to sum a range bigger than one column

[Katie]

act 1 2
act 3 4
hr 5 6
act 7 8

I want to sum all the numbers in both columns B and C if the office code is
"hr"
in otherwords, sumif(a1:a4,"hr",b1:c4) to produce 11 (or 5 + 6).
Excel returns just the first column, or 5.
Do not want to break into two "sumif" statements...too much data. Array??

 

[Mike]

Couple of ways you can handle this:

-----assuming that the titles are in A1-A4, and the values are in B1-C4-----

1) Array formula -- type in this formula: =sum(if($A$1:$A$4="hr",$B$1:$C$4))
---then hit ctrl+shift+enter.
2) 2 sumifs --- =SUMIF($A$1:$A$4,"hr",$B$1:$B$4)+SUMIF
$A$1:$A$4,"hr",$C$1:$C$4)

Good luck!

 

[RagDyer]

Assign D1 to hold your criteria (hr), and try this:

=SUMPRODUCT((A1:A4=D1)*(B1:C4))

 

[ShaneDevenshire]

Hi,

You could use either of the following plus many others:
=SUM((A1:A4="hr")*(B1:B4+C1:C4))
This formula is array entered - press Shift+Ctrl+Enter to enter it

The non-array entered equivalent
=SUMPRODUCT((A1:A4="hr")*(B1:B4+C1:C4))