sumif with a range of cells as criteria

[bluephoenix]

Hi,

What formula should I use to sum up the amount in a trial balance that relate to a range of cells. In this case i want to sum up the amounts in multiple accounts. I know i cant do a vlookup since i can only do 1 value and a sumif would work but then i'd rather not have to type each account number in the criteria since there are about 15 retained earnings accounts in the trial balance.
This is a excerpt from the trial balance.Lets say its the account number starts on cell A2 and the amount is on C2. I need to sum up the amounts in A2:A5.

account # Description Amount
[TABLE="width: 351"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]41100000[/TD]
[TD]Retained Earnings BOY[/TD]
[TD="align: right"]1,702,194[/TD]
[/TR]
[TR]
[TD]41100001[/TD]
[TD]Retained Earnings[/TD]
[TD="align: right"]147,419[/TD]
[/TR]
[TR]
[TD]41100003[/TD]
[TD]Retained Earnings Adj[/TD]
[TD="align: right"](837,079)[/TD]
[/TR]
[TR]
[TD]41100005[/TD]
[TD]Dtc - Retained Earnings[/TD]
[TD="align: right"]9,223[/TD]
[/TR]
</tbody>[/TABLE]

Thanks

 

[Scott T]

Can you go off the account name? As long as no other account names have "retained earnings" in them and all the accounts you want to sum has "retained earnings" then this should work.

=SUMIF(B2:B6,"*retained earnings*",C2:C6)

 

[MARZIOTULLIO]

c7[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD] Account[/TD]
[TD]Description[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

41100000​

[/TD]
[TD]Retained Earnings BOY[/TD]
[TD]

1,702,194​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

41100001​

[/TD]
[TD]Retained Earnings[/TD]
[TD]

147,419​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

41100003​

[/TD]
[TD]Retained Earnings Adj[/TD]
[TD]

-837,079​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

41100005​

[/TD]
[TD]Dtc - Retained Earnings[/TD]
[TD]

9,223​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1,021,757​

[/TD]
[/TR]
</tbody>[/TABLE]

c7=SUMPRODUCT(SUMIF(A2:A5,A2:A5,C2:C5))

 

[bluephoenix]

thank you both!