Marie2000
New Member
- Joined
- Nov 26, 2022
- Messages
- 1
- Office Version
- 365
- 2021
- 2019
- 2016
- 2013
- Platform
- Windows
- Mobile
- Web
Hello Everyone,
I have an Excel question. I have two sheets- tab a is the source data, tab b is data from a BI report. I need to match the IDs on both sheets, both in column A, and THEN sum all the matching values in the balances column to compare the report bal to the source bal. 1) the IDs on both files do not have equal lengths & may have some duplicates if there was more than one invoice. Therefore, I'm only matching the len of the source ID to the Report. Also, 2) Both have fields that are stored as BOTH numbers&Text. So, Im using Valuetotext. However, if the currency column is NOT EQUAL to USD, I need to, in addition to summing the balances, subtract the sum of the values in the losses column. Also, Here is what I have so far:SUMPRODUCT(((VALUETOTEXT($A38,0)=(LEFT(VALUETOTEXT(ExpPROJ_ID,0),LEN(VALUETOTEXT($A38)))))*ExpAdjBal)). I am completely stuck on how to subtract the losses based on the criteria I provided. I have started this but, I know I'm missing something simple. Any help is most appreciated!
I have an Excel question. I have two sheets- tab a is the source data, tab b is data from a BI report. I need to match the IDs on both sheets, both in column A, and THEN sum all the matching values in the balances column to compare the report bal to the source bal. 1) the IDs on both files do not have equal lengths & may have some duplicates if there was more than one invoice. Therefore, I'm only matching the len of the source ID to the Report. Also, 2) Both have fields that are stored as BOTH numbers&Text. So, Im using Valuetotext. However, if the currency column is NOT EQUAL to USD, I need to, in addition to summing the balances, subtract the sum of the values in the losses column. Also, Here is what I have so far:SUMPRODUCT(((VALUETOTEXT($A38,0)=(LEFT(VALUETOTEXT(ExpPROJ_ID,0),LEN(VALUETOTEXT($A38)))))*ExpAdjBal)). I am completely stuck on how to subtract the losses based on the criteria I provided. I have started this but, I know I'm missing something simple. Any help is most appreciated!
