Combine Columns Based on Criteria

[halama]

I have a table with columns for certain states. Each state belongs to a certain area. I now need to combine the columns for each area into one column and add the values for the rows where an input criteria is met. How do I do this? Here is an example:

Table to be combined:

	Criteria	CA	CT	NJ	NV	NY
Q1/13	c1	2	3	4	5	3
Q1/13	c2	4	3	4	2	6
Q2/13	c1	4	3	6	3	3
Q2/13	c2	2	4	3	5	3
Q3/13	c1	4	3	4	2	5
Q3/13	c2	3	5	2	5	3
Q4/13	c1	2	4	3	3	4
Q4/13	c2	3	2	4	3	4

Inputs:

Area1	CA,NV
Area2	CT,NJ,NY
Criteria	c1

Result:

	Area1	Area2
Q1/13	?	?
Q2/13	?	?
Q3/13	?	?
Q4/13	?	?

[RobertMika]

sum.xlsx
Please see attached.
=SUM(IF(ISNUMBER(SEARCH($C$1:$G$1,B$12)),IF($B$2:$B$9=$B$14,IF($A$2:$A$9=$A18,$C$2:$G$9))))
Confirm Control+Shift+enter

[halama]

Robert, I really appreciate your response and it works fine. However, I don't quite understand it. I did read up on array formulas but my understanding is that the array dimensions all need to match. In this case there is a 2D size as well as columns and rows each with different number of entries. I don't quite understand how this thing is being evaluated with these different dimensions.

[RobertMika]

In this case you have one vertical and one horizontal array and a matrix for data.
If you fallow the steps(Use Evaluate Formula toll) you will see that we have here multiplication of ranges.
Anything which is on crosses of those two arrays is being added up.

Please leave reputation if you happy with the answer.