| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple cond's for SUMIF or VLookup
I am attempting to gather net change info for a static list from a variable
list of imported data. I need to test if: sheet1A2=sheet2A2 AND sheet1b2=sheet2B2 then [sumf2:g2 or lookupf2+lookupg2] the basic format is: [acct #] [dept unit] [desc] [beg bal] [debit] [credit] [end bal] My example is for a departmentalized trial balance where there are multiple departments for the same account number. I do not want all data for the group of acct numbers, they need to be distinct according to the account number and the department. The imported trial balance will vary every month. Any help greatly appreciated. -- Mudshark |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple cond's for SUMIF or VLookup
=IF(Sheet1!A2=Sheet2!A2,IF(Sheet1!B2=Sheet2!B2,F2+ G2,"Not resolved"))
maybe this "MarkS" wrote: I am attempting to gather net change info for a static list from a variable list of imported data. I need to test if: sheet1A2=sheet2A2 AND sheet1b2=sheet2B2 then [sumf2:g2 or lookupf2+lookupg2] the basic format is: [acct #] [dept unit] [desc] [beg bal] [debit] [credit] [end bal] My example is for a departmentalized trial balance where there are multiple departments for the same account number. I do not want all data for the group of acct numbers, they need to be distinct according to the account number and the department. The imported trial balance will vary every month. Any help greatly appreciated. -- Mudshark |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple cond's for SUMIF or VLookup
Thank you, Mike. Unfortunatley it doesn't resolve the issue of testing an
array. I am also trying (without success) SumProduct variables. -- Mudshark "Mike" wrote: =IF(Sheet1!A2=Sheet2!A2,IF(Sheet1!B2=Sheet2!B2,F2+ G2,"Not resolved")) maybe this "MarkS" wrote: I am attempting to gather net change info for a static list from a variable list of imported data. I need to test if: sheet1A2=sheet2A2 AND sheet1b2=sheet2B2 then [sumf2:g2 or lookupf2+lookupg2] the basic format is: [acct #] [dept unit] [desc] [beg bal] [debit] [credit] [end bal] My example is for a departmentalized trial balance where there are multiple departments for the same account number. I do not want all data for the group of acct numbers, they need to be distinct according to the account number and the department. The imported trial balance will vary every month. Any help greatly appreciated. -- Mudshark |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple cond's for SUMIF or VLookup
Hi Mark
[acct #] [dept unit] [desc] [beg bal] [debit] [credit] [end bal] With your Sheet2 being the summary and Acct# in A2 and Dept unit in B2 =SUMPRODUCT((Sheet1!$A$2:$A$1000=A2)*(Sheet1!$B$2: $B$!000=B2)*Sheet1!E2:E1000) will give the total debit value for each dept under each Acct# Change the E2:E1000 to other ranges to pick up other values -- Regards Roger Govier "MarkS" wrote in message ... Thank you, Mike. Unfortunatley it doesn't resolve the issue of testing an array. I am also trying (without success) SumProduct variables. -- Mudshark "Mike" wrote: =IF(Sheet1!A2=Sheet2!A2,IF(Sheet1!B2=Sheet2!B2,F2+ G2,"Not resolved")) maybe this "MarkS" wrote: I am attempting to gather net change info for a static list from a variable list of imported data. I need to test if: sheet1A2=sheet2A2 AND sheet1b2=sheet2B2 then [sumf2:g2 or lookupf2+lookupg2] the basic format is: [acct #] [dept unit] [desc] [beg bal] [debit] [credit] [end bal] My example is for a departmentalized trial balance where there are multiple departments for the same account number. I do not want all data for the group of acct numbers, they need to be distinct according to the account number and the department. The imported trial balance will vary every month. Any help greatly appreciated. -- Mudshark |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple cond's for SUMIF or VLookup
Beautiful, Roger. I've been stumped for days now.
FYI, this is what I ended up with: =SUMPRODUCT(('[trial balance cvi.xls]Sheet 1'!$B$2:$B$1000=A139)*('[trial balance cvi.xls]Sheet 1'!$C$2:$C$1000=B139),'[trial balance cvi.xls]Sheet 1'!$F$2:$F$1000)+SUMPRODUCT(('[trial balance cvi.xls]Sheet 1'!$B$2:$B$1000=A139)*('[trial balance cvi.xls]Sheet 1'!$C$2:$C$1000=B139),'[trial balance cvi.xls]Sheet 1'!$G$2:$G$1000) I think my problem was understanding the "*" syntax needed. Thanks again. -- Mudshark "Roger Govier" wrote: Hi Mark [acct #] [dept unit] [desc] [beg bal] [debit] [credit] [end bal] With your Sheet2 being the summary and Acct# in A2 and Dept unit in B2 =SUMPRODUCT((Sheet1!$A$2:$A$1000=A2)*(Sheet1!$B$2: $B$!000=B2)*Sheet1!E2:E1000) will give the total debit value for each dept under each Acct# Change the E2:E1000 to other ranges to pick up other values -- Regards Roger Govier "MarkS" wrote in message ... Thank you, Mike. Unfortunatley it doesn't resolve the issue of testing an array. I am also trying (without success) SumProduct variables. -- Mudshark "Mike" wrote: =IF(Sheet1!A2=Sheet2!A2,IF(Sheet1!B2=Sheet2!B2,F2+ G2,"Not resolved")) maybe this "MarkS" wrote: I am attempting to gather net change info for a static list from a variable list of imported data. I need to test if: sheet1A2=sheet2A2 AND sheet1b2=sheet2B2 then [sumf2:g2 or lookupf2+lookupg2] the basic format is: [acct #] [dept unit] [desc] [beg bal] [debit] [credit] [end bal] My example is for a departmentalized trial balance where there are multiple departments for the same account number. I do not want all data for the group of acct numbers, they need to be distinct according to the account number and the department. The imported trial balance will vary every month. Any help greatly appreciated. -- Mudshark |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple cond's for SUMIF or VLookup
Hi Mark
You're very welcome. Thanks for the feedback and letting us know how you finally solved your problem. -- Regards Roger Govier "MarkS" wrote in message ... Beautiful, Roger. I've been stumped for days now. FYI, this is what I ended up with: =SUMPRODUCT(('[trial balance cvi.xls]Sheet 1'!$B$2:$B$1000=A139)*('[trial balance cvi.xls]Sheet 1'!$C$2:$C$1000=B139),'[trial balance cvi.xls]Sheet 1'!$F$2:$F$1000)+SUMPRODUCT(('[trial balance cvi.xls]Sheet 1'!$B$2:$B$1000=A139)*('[trial balance cvi.xls]Sheet 1'!$C$2:$C$1000=B139),'[trial balance cvi.xls]Sheet 1'!$G$2:$G$1000) I think my problem was understanding the "*" syntax needed. Thanks again. -- Mudshark "Roger Govier" wrote: Hi Mark [acct #] [dept unit] [desc] [beg bal] [debit] [credit] [end bal] With your Sheet2 being the summary and Acct# in A2 and Dept unit in B2 =SUMPRODUCT((Sheet1!$A$2:$A$1000=A2)*(Sheet1!$B$2: $B$!000=B2)*Sheet1!E2:E1000) will give the total debit value for each dept under each Acct# Change the E2:E1000 to other ranges to pick up other values -- Regards Roger Govier "MarkS" wrote in message ... Thank you, Mike. Unfortunatley it doesn't resolve the issue of testing an array. I am also trying (without success) SumProduct variables. -- Mudshark "Mike" wrote: =IF(Sheet1!A2=Sheet2!A2,IF(Sheet1!B2=Sheet2!B2,F2+ G2,"Not resolved")) maybe this "MarkS" wrote: I am attempting to gather net change info for a static list from a variable list of imported data. I need to test if: sheet1A2=sheet2A2 AND sheet1b2=sheet2B2 then [sumf2:g2 or lookupf2+lookupg2] the basic format is: [acct #] [dept unit] [desc] [beg bal] [debit] [credit] [end bal] My example is for a departmentalized trial balance where there are multiple departments for the same account number. I do not want all data for the group of acct numbers, they need to be distinct according to the account number and the department. The imported trial balance will vary every month. Any help greatly appreciated. -- Mudshark |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vlookup on multiple worksheets? | Excel Discussion (Misc queries) | |||
| vLookup with multiple lookup value in table array | Excel Worksheet Functions | |||
| Want Vlookup to list multiple items with the same key? | Excel Worksheet Functions | |||
| VLOOKUP - Multiple cells in lookup value | Excel Worksheet Functions | |||
| vLookup - display multiple rows | Excel Worksheet Functions | |||
Linear Mode
