Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SUMIF using two columns in both Range and Criteria
I have a speadsheet with columns as under:
Account Code Department Amount ------------ ---------- ------ 7000 12 £100 7050 5 £250 There are a couple of hundred rows with different account numbers and a variety of Department numbers. Is there a way I can use SUMIF to summarize the amounts by Code AND Department? In other words I want to sum ALL the amounts with Code 7000 and Dept 12 etc. Thanks! |
#2
|
|||
|
|||
=SUMPRODUCT(--(A1:A20&B1:B20="700012"),C1:C20)
-- HTH Bob Phillips "Gordon" wrote in message ... I have a speadsheet with columns as under: Account Code Department Amount ------------ ---------- ------ 7000 12 £100 7050 5 £250 There are a couple of hundred rows with different account numbers and a variety of Department numbers. Is there a way I can use SUMIF to summarize the amounts by Code AND Department? In other words I want to sum ALL the amounts with Code 7000 and Dept 12 etc. Thanks! |
#3
|
|||
|
|||
Bob Phillips wrote:
=SUMPRODUCT(--(A1:A20&B1:B20="700012"),C1:C20) I'd just found "sumproduct" but your version of it is MUCH neater. Thanks! |
#4
|
|||
|
|||
As an aside, this version
=SUMPRODUCT(--(A1:A20=7000),--(B1:B20=12),C1:C20) although longer is more efficient I am sure -- HTH Bob Phillips "Gordon" wrote in message ... Bob Phillips wrote: =SUMPRODUCT(--(A1:A20&B1:B20="700012"),C1:C20) I'd just found "sumproduct" but your version of it is MUCH neater. Thanks! |
#5
|
|||
|
|||
And might be safer, too.
If the OP had something like this, Account Dept 7000 12 70001 2 7 00012 the original formula might yield incorrect results. Bob Phillips wrote: As an aside, this version =SUMPRODUCT(--(A1:A20=7000),--(B1:B20=12),C1:C20) although longer is more efficient I am sure -- HTH Bob Phillips "Gordon" wrote in message ... Bob Phillips wrote: =SUMPRODUCT(--(A1:A20&B1:B20="700012"),C1:C20) I'd just found "sumproduct" but your version of it is MUCH neater. Thanks! -- Dave Peterson |
#6
|
|||
|
|||
You can stay with SumIf if you create an additional column...
Let A1:C3 house the sample you provided. D1: Concat D2, copied down: =A2&"#"&B2 Now invoke... =SUMIF($D$2:$D$3,$F2&"#"&G$1,$C$2:$C$3) where F2 houses an account number and G1 a department. If you are on Excel 2003, convert A1:D3 into a list using Data|List|Create List. Gordon wrote: I have a speadsheet with columns as under: Account Code Department Amount ------------ ---------- ------ 7000 12 £100 7050 5 £250 There are a couple of hundred rows with different account numbers and a variety of Department numbers. Is there a way I can use SUMIF to summarize the amounts by Code AND Department? In other words I want to sum ALL the amounts with Code 7000 and Dept 12 etc. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF using two columns in both Range and Criteria | Excel Discussion (Misc queries) | |||
Sumif function with two criteria from different columns | Excel Worksheet Functions | |||
SUMIF with only one criteria cell within range | Excel Discussion (Misc queries) | |||
can I use a range of dates as a criteria when using sumif? | Excel Worksheet Functions | |||
SUMIF multiple criteria in 1 range | Excel Worksheet Functions |