SUM per City

[Forestq]

hi,

I have simply formula SUM:

=SUMPRODUCT('Sheet3'!$BF$2:$BF$100*'Sheet3'!$AX$2:$AX$100*(Specific_Name=A10))

In coulmn BB I have name of the CITY - Tokyo, Paris, Londyn etc...

Now I want to SUM exactly this same but depending on the selected city (example: 'Sheet1'!A1= 'Paris')

Could you please help me??

 

[Akashwani]

Hi Forestq,

Does this help to point you in the right direction?...

Excel Workbook
	A	B	C	D	E	F
1	City	Total		City	Data
2	Paris	800		London	100
3		800		Paris	200
4		800		Rome	300
5				Oslo	400
6				Paris	200
7				Paris	200
8				Rome	300
9				Oslo	400
10				London	100
11				Paris	200
12
Sheet10

Ak

 

[Bill Rockenbach]

or =SUM(Sheet3!$BF$2:$BF$100*Sheet3!$AX$2:$AX$100*IF(Sheet3!$BB$2:$BB$100=A1,1,0)) enter as array formula

 

[Forestq]

thx Akashwani, little help


i just multiply all function by my value (a2 is value with my city)

=SUMPRODUCT('Sheet3'!$BF$2:$BF$100*'Sheet3'!$AX$2:$AX$100*(Specific_Name=A10) * (A2 = sheet3!$BB$2:$BB$100))