multiple criteria MATCH macro

[hgchas]

hello I am trying to work this code but haven't found anything to what I am trying to accomplish. I have attached the workbook and the code I am working on-might be something off. what I am trying to accomplish is to bring the sales number from sheet2 to sheet 1 column D meeting the criteria from sheet 1 columns A and C.

Please Login or Register  to view this content.

[Fluff13]

Why not just put this formula in D2 & copy down
=SUMIFS(Sheet2!C:C,Sheet2!A:A,A2,Sheet2!B:B,C2)

[hgchas]

the sumif function worked with numbers but I am wanting to do the same with the name (text), does anyone have recommendations?

[karmapala]

Hi,

Please Login or Register  to view this content.

sorry, I don't see customerID in Sheet2 ?

Or maybe did you mean "region" ?

Please Login or Register  to view this content.

What Fluff13 suggest is good if you just want to get the total sales from one name and his/her region. But....

the sumif function worked with numbers but I am wanting to do the same with the name (text), does anyone have recommendations?

But from your quote above, it seems what you want is not just to get the total sales ?

If yes, I think it would be better if you describe about it also.

Anyway, for the time being I think the modified code of yours below is maybe (I guess) what you want :

Please Login or Register  to view this content.

[hgchas]

this is the other way I am attempting to work this out. I have attached the workbook and what I want to do is bring customerID from sheet2 referencing columns A and C from sheet1.

[Fluff13]

How about
=INDEX(Sheet2!$D$2:$D$11,MATCH(A2&"|"&C2,Sheet2!$A$2:$A$11&"|"&Sheet2!$B$2:$B$11,0))

Needs Ctrl Shift Enter, rather than just enter.

[hgchas]

I have the Ctrl Shift Enter option but was looking to see if there was a simpler option to automate.

[Fluff13]

How about

Please Login or Register  to view this content.

[karmapala]

this is the other way I am attempting to work this out. I have attached the workbook and what I want to do is bring customerID from sheet2 referencing columns A and C from sheet1.

Is this a different case than your previous sample workbook ?
A. In your first sample workbook, the CustomerID is in Sheet1,
and I guess the result you want is to have the total sales in sheet1 (yellow highlighted column D).

B. In your second sample workbook, the CustomerID is in Sheet2,
and now the result you want is to have the CustomerID in sheet1 (yellow highlighted column B).

Anyway, my way is using "find" for point-B :

Please Login or Register  to view this content.

Using three criteria, in case for example there is another John in the West but the customerID is different .

[hgchas]

hey everyone thanks for the input! Both solutions worked! thank you!

[Fluff13]

You're welcome & thanks for the feedback.