Sum With Crietria

[Kumara_faith]

Hi,

I have the following Data Table and Summary table.


I can use Pivot Table but I need a formula to perform the following:

Example for Dallas
1) Look at Data Table, search for column title "Branch".
2) Then, under column title "Branch", look for "Dallas"
3) Then look for column title "P1" and summarize how many for "Dallas" under "P1".

The added challenge here is that the location of the column title in the Data Table table can often change. Sometimes P1 can be in column C, and sometime it could be in another column.

Is there a way to accomplish this ? Appreciate all help.

I have a cross post here:

http://www.mrexcel.com/forum/excel-q...ml#post4117529

[mcmahobt]

If I understood your question correctly, you may want to reorganize your data if possible. Something like the attached may work.

[AlKey]

Use this formula n E18

Pull it across and down

=SUMPRODUCT(($G$5:$G$12=$D18)*($C$4:$I$4=E$17),$C$5:$I$12)

[Kumara_faith]

Dear mcmahobt,

Thank you for your suggestions However, to rearrange the data would take a lot of time as the actual data table is very large and the report needs to be prepared on daily basis.

Dear AlKey,

If the branch column is changed to another column, the formula does not work. For example if on a particular day, the branch data column is not in column G but in column F, then the formula does not work.

Appreciate your help and patience.

[samba_ravi]

E18=SUMIFS(INDEX($C$5:$J$12,,MATCH(E$17,$C$4:$J$4,0)),INDEX($C$5:$J$12,,MATCH($D$17,$C$4:$J$4,0)),$D18)
Try this and copy across

[AlKey]

@Kumara_faith

The formula was written for the data you provided. There is now way for anyone to know what you going to change it to.

[Glenn Kennedy]

nflsales' solution is (as ever) very nice. Here's another possibility - rather more long-winded, though...