• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Adding Values based GL account numbers

Hello,
I am trying to add values from a table based on account numbers. For example I have data like this

Date Acct Amount
1/1/2018 1500-00 1500
1/15/2018 1500-01 2000
2/15/2018 1500-03 1000

Output for January
Acct Amount
1500-00 + 1500-01 3500

If I type 1500-00 + 1500-01 in a cell I should get the 3500 value for January. The date criteria should also be considered

Thanks for your help. I have attached a sample file
 

Attachments

  • chandootest.xlsx
    11.2 KB · Views: 8
The date criteria should also be considered
It isn't clear. Also, 1500-00 + 1500-01 should be 3000, I believe.

I'd recommend splitting ACCT condition into two cells. But if you need to do it in single cell...

In J4:
=SUMIFS($D$4:$D$10,$A$4:$A$10,TRIM(LEFT(SUBSTITUTE(H4," ",REPT(" ",50)),50)))+SUMIFS($D$4:$D$10,$A$4:$A$10,TRIM(RIGHT(SUBSTITUTE(H4," ",REPT(" ",50)),50)))

Copy to J6.
 
Mr. Chihiro,
Thanks for your response.

The 1500-00, 1500-01 are account numbers that have $1500 and $2000 values. You formula partially worked for me. If I add another GL account I am not getting the correct amount. I have attached an updated sample data file.

Thanks for your help.
 

Attachments

  • chandootest.xlsx
    12.8 KB · Views: 6
Last edited by a moderator:
The 1500-00, 1500-01 are account numbers that have $1500 and $2000 values.

Not according to your workbook ;)
upload_2018-9-14_14-6-20.png

If I add another GL account I am not getting the correct amount.
Use another construct then.
=SUMPRODUCT(ISNUMBER(SEARCH($A$4:$A$10,H4))*($D$4:$D$10))
 
Not according to your workbook ;)
View attachment 55207


Use another construct then.
=SUMPRODUCT(ISNUMBER(SEARCH($A$4:$A$10,H4))*($D$4:$D$10))

Sorry I was looking what I typed in the problem. Is it possible to extract values based on date range. This formula seems to be working for me. I am still add more GL accts to test.

Again really appreciate take your time to find a solution to my problem.

Thanks again
 
Just add another condition to Sumproduct. If you need to filter based on dates as well.

Ex:
=SUMPRODUCT(ISNUMBER(SEARCH($A$4:$A$10,H4))*(MONTH($B$4:$B10)=1)*($D$4:$D$10))

Where you want Month=1. Or use cell reference in place of 1.
 
Thanks for your help. Your formula helped me create an income statement in 20 mins which would have taken me 3 hours to create.

Thanks again
 
Last edited by a moderator:
Back
Top