Sum cells multiple criteria different sheet

[karstens]

I have tried many options but can't seem to figure this out.
I have two tabs:
Trial balance and Analysis

On the Analysis sheet, I have a cell where I want it to look on the Trial balance sheet, find all codes these codes (7813.1, 7813.2, 7813.89) which are in column A:A, then give them the totals of what is in G:G.

Should be a simple formula, but it isn't working.
Thanks to anyone who can help me.

[NBVC]

Something like:

=SUMPRODUCT(--(ISNUMBER(MATCH('Trial Balance'!A1:A100,{"7813.1","7813.2","7813.3"},0))),'Trial Balance'!G1:G100)

edit ranges to suit and trial balance actual sheet name...

This assumes your are entering Account Id's as text string.. if not, remove the quotes from around each account id in above formula.

[karstens]

Thank you so much for the reply, I think this is REALLY close...but I get FALSE as the result with this.

[karstens]

Never mind!
I got it to work.
I had changed the range like you stated, but I hadn't changed it enough.
Now it works great!
Thank you SO much!

[NBVC]

Not sure how you would be getting FALSE, unless you put it within an IF() formula or you did not enter the formula the way I presented it...

If you have difficulty, please post a sample workbook showing the issue.

[NBVC]

Never mind!
I got it to work.
I had changed the range like you stated, but I hadn't changed it enough.
Now it works great!
Thank you SO much!

Ok great...so you got it to work

[NBVC]

Please remember to mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[karstens]

Do you know if it is possible to go one step further with this? I ran into another need with the same spreadsheet.

Our codes are set up like this: XXXX.XXXX XX.XX.XX.
My trial balance tab has the 4.4 code in column A, and the 2.2.2 code in Column B.

So if my code is 6121.0000 00.00.00 and 6121.0000 00.00.01 and 6121.0000 00.00.02, I may need to get the value of column G for each code sep
arately and sometimes combines them like below:

Column A: Account Code B: Sub Account C
Row1 / 6121.0000 / 00.00.00 / 10.00
Row2 / 6121.0000 / 00.00.01 / 15.00
Row3 / 6121.0000 / 00.00.02 / 20.00

So using the same concept in the question you solved for me earlier, can we expand it further to answer the following:

At times, would need 6121.0000 with 00.00.00 only...for a total of $10.00
But I also need to have 6121.0000 with both sub accounts .01 and .02 added together to give me a result of $35.00.

Is this possible? Or did I totally lose you in my explanation?
Thanks again! I can't tell you HOW much I appreciate the help.

[NBVC]

Try any/all of the below

This one, searches for the string "6121.0000" in all cells A1:A100 and sums the corresponding values in G1:G100 where that string exists...

=SUMPRODUCT(--(ISNUMBER(SEARCH("6121.0000",'Trial Balance'!A1:A100))),'Trial Balance'!G1:G100)

or
This one only looks for specifically "6121.0000" in the cell and nothing else...

=SUMPRODUCT(--('Trial Balance'!A1:A100="6121.0000"),'Trial Balance'!G1:G100)

or

This one is similar to first one, but only looks at the left 9 characters to define a match.. so if left 9 chars is "6121.0000", then a match exists...

=SUMPRODUCT(--(LEFT('Trial Balance'!A1:A100,9)="6121.0000"),'Trial Balance'!G1:G100)

hope these help.

[karstens]

If I am reading it right, it does not look at Column B in addition to Column A.
I would need to find the total of all 6121 which is column A, that also has the sub account of 00.00.01 and 00.00.02 which is in column B, then total what is in G:G for the ones meeting the criteria.

Does that make sense? I know it is hard to write the needs clearly.
Thanks again.

[NBVC]

Then perhaps...

=SUMPRODUCT(--('Trial Balance'A1:A100="6120"),--(ISNUMBER(MATCH('Trial Balance'!B1:B100,{"00.00.01","00.00.02"},0))),'Trial Balance'!G1:G100)

adjusting again ranges, sheetnames to suit.

[karstens]

Sweet! You are awesome!
I only had to make one small adjustment, and that was the ! was omitted from the section regarding column A.
Thanks again.
Have a nice weekend!

[NBVC]

Yeah... cause I typed it without testing

Anyways, great that it works now!

Cheers.