Multiple cond's for SUMIF or VLookup

G

Guest

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.
 
G

Guest

Thank you, Mike. Unfortunatley it doesn't resolve the issue of testing an
array. I am also trying (without success) SumProduct variables.
--
Mudshark


Mike said:
=IF(Sheet1!A2=Sheet2!A2,IF(Sheet1!B2=Sheet2!B2,F2+G2,"Not resolved"))

maybe this

MarkS said:
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.
Click to expand...
Click to expand...
 
R

Roger Govier

Hi Mark
[acct #] [dept unit] [desc] [beg bal] [debit] [credit] [end
bal]
Click to expand...


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 said:
Thank you, Mike. Unfortunatley it doesn't resolve the issue of
testing an
array. I am also trying (without success) SumProduct variables.
--
Mudshark


Mike said:
=IF(Sheet1!A2=Sheet2!A2,IF(Sheet1!B2=Sheet2!B2,F2+G2,"Not resolved"))

maybe this

MarkS said:
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.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
Hi Mark
[acct #] [dept unit] [desc] [beg bal] [debit] [credit] [end
bal]
Click to expand...


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 said:
Thank you, Mike. Unfortunatley it doesn't resolve the issue of
testing an
array. I am also trying (without success) SumProduct variables.
--
Mudshark


Mike said:
=IF(Sheet1!A2=Sheet2!A2,IF(Sheet1!B2=Sheet2!B2,F2+G2,"Not resolved"))

maybe this

:

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.
Click to expand...
Click to expand...
Click to expand...
 
R

Roger Govier

Hi Mark

You're very welcome. Thanks for the feedback and letting us know how you
finally solved your problem.

--
Regards

Roger Govier


MarkS said:
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 said:
Hi Mark
[acct #] [dept unit] [desc] [beg bal] [debit] [credit] [end
bal]
Click to expand...


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 said:
Thank you, Mike. Unfortunatley it doesn't resolve the issue of
testing an
array. I am also trying (without success) SumProduct variables.
--
Mudshark


:

=IF(Sheet1!A2=Sheet2!A2,IF(Sheet1!B2=Sheet2!B2,F2+G2,"Not
resolved"))

maybe this

:

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.
Click to expand...
Click to expand...
Click to expand...
 
Top