Excel SUMIFS Formula help 1

[tonyvee1973]

Hi Guys
Just a simple one i hope.
I have 2 sheets in excel, i want to look at column A on both sheets and if they match then insert the contents of column D from sheet 2.
I have tried: =SUMIFS(Sheet2!$D:$D,Sheet2!$A:$A,$A1) but this doesnt pull in the data from column D from sheet 2.

Any ideas?

Thanks as always

 

[SkipVought]

SUMIFS does not do column matching.

What SUMIFS does is it SUMS on one range and looks at 2 or more ranges, and for each range matches a criteria value, for instance if your columns had this data...
[tt]
1 a w
2 s e
3 d r
4 s e
5 a w
6 s q
7 d w
[/tt]
then
[tt]
=SUMIFS(A1:A7,B1:B7,"a",C1:C7,"w")
[/tt]
returns 6


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

What data is in columns A & D in sheet2 and what value is in A1?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tonyvee1973]

Hi Skip, thanks for the info.
Column A is an account code (Number) on sheet 1 and 2
Column D on sheet 2 is a reference (Alphanumeric)

Any other ideas on how i can look at Column A on both sheets and if they match then pull the data from the corresponding cell in sheet 2 column D?

 

[SkipVought]

This function is SUMIFS. It is trying to SUM the data in Column D!!!!

You wnat a LOOKUP it seems...
[tt]
=INDEX(Sheet2!$D:$D,Match(A1,Sheet2!$A:$A,0),1)
[/tt]
This assumes that there is ONE row for each account code in Sheet2 or if more than one, that only the data from the FIRST occurrence will be returned.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tonyvee1973]

Thanks Skip, that seems to have worked a treat!
I'm sure i used the SUMIFS for thsi sort of thing before but like you say it must have been summing instead.
Thanks for the help, really appreciated
Tony