sumif with a range of cells as criteria

bluephoenix

New Member
Joined
Sep 28, 2017
Messages
2
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
41100000Retained Earnings BOY1,702,194
41100001Retained Earnings147,419
41100003Retained Earnings Adj(837,079)
41100005Dtc - Retained Earnings9,223

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

Thanks
 

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,606
Office Version
365, 2016
Platform
Windows
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
Joined
Aug 22, 2015
Messages
767
c7
A
B
C
1
AccountDescriptionAmount
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))
 

Forum statistics

Threads
1,085,307
Messages
5,382,838
Members
401,807
Latest member
xlWatcher

Some videos you may like

This Week's Hot Topics

Top