Array Formula

[leimst]

Hello,

I have the following data:

[pre]

Date	   Merchant Code	 Amount		Separate Merchant Code List
5/23/2012	5712	         $25.24 		4596
6/24/2012	5963	         $15.21 		8521
7/15/2012	2541	         $12.00 		2541
8/6/2012	8512	         $32.65 		3657
7/6/2012	7532	         $56.45 		1254
4/22/2012	1598	         $41.57 		4562
7/17/2012	4562	         $13.00 		3695
9/8/2012	4587	         $19.75 		7563

[/pre]

I would like Excel to sum the dollar amount for every date occurring within July, and for every Merchant Code that also occurs in the "Separate Merchant Code List" and I believe that an array formula would be the best if not the only way to approach this challenge?


Thank you in advance for any assistance,


Leimst

 

[NARAYANK991]

Hi Leimst ,


You can get a list of merchant codes which also appear in the Separate Merchant Codes List , by the following formula , entered as an array formula , using CTRL SHIFT ENTER :


=INDEX(Merchant_Codes,SMALL(IF(ISNUMBER(MATCH(Merchant_Codes,Separate_Merchant_Codes_List,0)),ROW(Merchant_Codes)-MIN(ROW(Merchant_Codes))+1),ROW(A1)))


Getting the sum of the amounts for the month of July for these merchant codes is by using the SUMIFS function :


=SUMIFS(Amounts,Dates,">="&DATE(YEAR(A2),7,1),Dates,"<="&DATE(YEAR(A2),7,31),Merchant_Codes,C15)


where A2 contains the first date in the dates column , and C15 contains the merchant code from the first formula.


Narayan

 

[Debraj]

Hi Leimst,


can you please check the below formula also..

=SUMPRODUCT(Amount,((MONTH(Date)=7)*(ISNUMBER(MATCH(Merchant_Code,Separate_Merchant_Code_List,0)))))


without NameRange.. its something like..


=SUMPRODUCT($C$2:$C$9,((MONTH($A$2:$A$9)=7)*(ISNUMBER(MATCH($B$2:$B$9,$D$2:$D$9,0)))))

Regards,

Deb