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

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!

"Katie" wrote:

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

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

=SUMPRODUCT((A1:A4=D1)*(B1:C4))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Katie" wrote in message
...
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??

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))
--
Cheers,
Shane Devenshire
Microsoft Excel MVP

"Katie" wrote:

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