SUMIFS Function returning #VALUE - Multiple columns to be summed

Hi experts,

I have this problem.

In one excel sheet I have multiple tables containing same number of columns and same column heading (eg, T1,T2,T3,T4,T5,T6). These values need to be summed up (i.e. All T1 in 1 column, All T2 in 1 Column ... grouped by 2 preceding columns "CITY" and "LOCATION" )

I have attached the Problem sheet If someone can give fix the formula in the attached sheet, I would highly appreciate.

Thanks in Advance.

Vetaal.

What is "some time"??

One possible alternatie might be this array formula:

=SUM(IF(MOD(COLUMN(DATA!D3:HF3)-COLUMN(D3),7)=0,DATA!D3:HF3,0))

Actually.... since the data are in the same order on both sheets:

=SUMPRODUCT(--(DATA!$D2:$HL2=D$2),DATA!$D3:$HL3)

will also work well (and fastest)

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.