Sumifs with two different table reference

[spycein]

Hi Everyone,
Looking for a sumifs formula which would return the state-wise sales value. I have two different tables.
one is Date wise, city wise Sales data and another is state name master table.

Date	City	Sales Amount
01-02-2017	Mumbai	84,885.00
01-02-2017	Pune	88,783.00
02-02-2017	Mangalore	97,541.00
02-02-2017	Bangalore	32,905.00
03-02-2017	Mangalore	31,641.00
03-02-2017	Chennai	70,263.00

<tbody>
</tbody>

State	City
Maharastra	Mumbai
Maharastra	Pune
Karnataka	Mangalore
Karnataka	Bangalore
Karnataka	Mangalore
Tamil Nadu	Chennai

<tbody>
</tbody>

i am looking for a formula which can return the value as per the below table data criteria.

Date	Maharastra	Karnataka	Tamil Nadu
01-02-2017
02-02-2017
03-02-2017

<tbody>
</tbody>

Thank you so much in advance.

Regards
Shib

 

[Eric W]

Try this:

	A	B	C	D	E	F	G	H	I	J	K
1	Date	City	Sales Amount		State	City		Date	Maharastra	Karnataka	Tamil Nadu
2	1/2/2017	Mumbai	84,885.00		Maharastra	Mumbai		1/2/2017	173,668.00	0.00	0.00
3	1/2/2017	Pune	88,783.00		Maharastra	Pune		2/2/2017	0.00	130,446.00	0.00
4	2/2/2017	Mangalore	97,541.00		Karnataka	Mangalore		3/2/2017	0.00	31,641.00	70,263.00
5	2/2/2017	Bangalore	32,905.00		Karnataka	Bangalore
6	3/2/2017	Mangalore	31,641.00		Tamil Nadu	Chennai
7	3/2/2017	Chennai	70,263.00
8

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula I2 {=SUM(SUMIFS($C$2:$C$7,$B$2:$B$7,IF($E$2:$E$6=I$1,$F$2:$F$6,"zzzzz"),$A$2:$A$7,$H2))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Put the formula in I2, change the references to match your sheet, and confirm with Control+Shift+Enter. Then drag to the rest of the table. Note that I had to remove the second Mangalore from the table.