# Adding Values based GL account numbers

#### sureshsonti

##### Member
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

• 11.2 KB Views: 8

#### Chihiro

##### Excel Ninja
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.

#### sureshsonti

##### Member
Mr. Chihiro,

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.

#### Attachments

• 12.8 KB Views: 6
Last edited by a moderator:

#### Chihiro

##### Excel Ninja
The 1500-00, 1500-01 are account numbers that have \$1500 and \$2000 values.

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))

#### sureshsonti

##### Member
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

#### Chihiro

##### Excel Ninja
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.

#### sureshsonti

##### Member
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: