Multiple Spreadsheet,Not sure what to use? Vlookup works for first

[Getz]

I have a excel problem with a formula for a spreadsheet, basically I am
trying to combine two accounting spreadsheets. Lets call them spreadsheet A &
B. All my values are in spreadsheet A and this contains four columns. see
below

Code Description Division Amount
1 COGS sales 10
1 COGS manufacturing 15
2 expenses sales 12
2 expenses manufacturing 17

Now I need the amount in spreadsheet B which is organized like this

SALES Manufacturing
COGS
EXPENSES

Now for sales i have put in=vlookup(A2,spreadsheet B!A25,4,False) and this
works but it doesnt work for manufacturing as it brings up the first thing it
find (for example it brings $10 and $12)

I gues my question is How do I create a formula that takes columns one and
three into account from another spreadsheet in order to get column four in a
different spreadsheet?

 

[bpeltzer]

I'm not sure if I'm reading your columns correctly... it looks like your
source data has the division in column C and description in column B (though
your vlookup formula suggests that the description is in column A). Based on
what it looks like:
=sumproduct(--('spreadsheet B'!$C$2:$C$5=B$1),--('spreadsheet
B'!$B$2:$B$5=$A2),'spreadsheet B'!$D$2:$D$5))
The first two arguments of the sumproduct are arrays of 1s and 0s; 1 where
there's a match, 0 where there's not. The third argument is the
corresponding amount. Sumproduct multiplies corresponding elements of each
array and adds them up. The multiplication results in 0 whenever either
match fails, but results in the 'amount' when both matches succeed. Then all
the amonts from the successful matches get added.
If you're open to a non-formula approach, also try a pivot table, with the
Description in the row field, Department in the column field and Amount in
the Data field.

 

[Getz]

Thanks for the quick reply and sorry for the confusion. I guess really column
1 and column 2 are the same and explain the account name, column 3 is the
accounting department and column 4 is the $amount. In the new spreadsheet I
only want to display the $amount.

Now to understand this sum product equation, the first two parts of the
formula define whether it is true, then if they are both true the third is
displayed? Is this how this reads. So for my example the amount in cell D2 is
$10, to get this i need a formula that says if A is 1 and C is sales, then D
is amount shown?

 

[bpeltzer]

You can build the values you're looking for directly into the formula; I was
trying to give you a formula that pulled the target values from the header
row and column in your output table. But for your specific example of sales
COGS:
=sumproduct(--('spreadsheet B'!$C$2:$C$5="sales"),--('spreadsheet
B'!$a$2:$a$5=1),'spreadsheet B'!$D$2:$D$5))
(I'd encourage you to try the prior formula; the same formula should work
for each cell in your output table!)

 

[Getz]

Thanks a ton, i am going to try to hammer this out. I am not and excel guru
though but at some point ill get this. I appreciate your time

 

[Ashish Mathur]

Hi,

You could also try to use the following array formula 9Ctrl+Shift+Enter)

=sum(if((range1="COGS")*(range2="Sales"),sum_range))

Please replace "COGS" and "Sales" with the references in your table

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com