# sumif with a range of cells as criteria

#### bluephoenix

##### New Member
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
 41100000 Retained Earnings BOY 1,702,194 41100001 Retained Earnings 147,419 41100003 Retained Earnings Adj (837,079) 41100005 Dtc - Retained Earnings 9,223

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Thanks

#### Scott T

##### Well-known Member
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.
Code:
``=SUMIF(B2:B6,"*retained earnings*",C2:C6)``

Last edited:

#### MARZIOTULLIO

##### Well-known Member
c7
 A​ B​ C​ 1​ Account Description Amount 2​ 41100000​ Retained Earnings BOY 1,702,194​ 3​ 41100001​ Retained Earnings 147,419​ 4​ 41100003​ Retained Earnings Adj -837,079​ 5​ 41100005​ Dtc - Retained Earnings 9,223​ 6​ 7​ 1,021,757​

<tbody>
</tbody>

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

thank you both!