Adding Multiple Columns and Multiple Rows - Using specific criteria

[gamingguy]

Hi Guys

Just curious if someone knew had to do a complicated "sumif" calculation where you take the criteria in a list from the columns and the rows. What I am trying to do is add up all the quarters in a specific year (say in this case 2018) and add only 2018's quarters along with the information for Variable 2. I would like to repeat this for years etc so eventually I get a small table that has the summation of years, and one row for each variable.

IS that possible in excel?

<tbody>

	2017	2017	2017	2017	2018	2018	2018	2018	2019	2019	2019	2019
	2017.Q1	2017.Q2	2017.Q3	2017.Q4	2018.Q1	2018.Q2	2018.Q3	2018.Q4	2019.Q1	2019.Q2	2019.Q3	2019.Q4
Variable 1
Variable 1	59	97					5
Variable 1	117	80	139	239	185	180	173	175	178	175	175	425
Variable 2		418	125	283	62	117	245			99	149	80
Variable 2								12	30	18	5	3
Variable 2							176	315	284	142	176	91
Variable 2	108	396	50	62		10
Variable 2	124	428	72	186	76	51	128	18		64	96	44
Variable 2		55	25	30	21	6	14		7	10	15	6
Variable 2		85
Variable 2							28
Variable 2		6	16
Variable 3		5							4
Variable 3	15
Variable 3				8			29	73	22
Variable 3				4
Variable 3						680	596	1,260	990	812	967	893
Variable 3		340	344	731	812	601	456	1,202	740	517	274	823
Variable 3	268	352	310	502	209	21	22	71	2	47	58	33

<colgroup><col><col><col><col span="6"><col span="4"></colgroup><tbody>
</tbody>

</tbody>

 

[RasGhul]

SUMIFS or SUMPRODUCT should be able to achieve this, can you post some sample data so we can come up with a solution for you.

 

[gamingguy]

SUMIFS or SUMPRODUCT should be able to achieve this, can you post some sample data so we can come up with a solution for you.

Looks like the data didn't come through. Here is the chart.

	ACTUAL	ACTUAL	ACTUAL	ACTUAL	ACTUAL	ACTUAL	ACTUAL	ACTUAL
	2017	2017	2017	2017	2018	2018	2018	2018
	2017.Q1	2017.Q2	2017.Q3	2017.Q4	2018.Q1	2018.Q2	2018.Q3	2018.Q4
Variable 1
Variable 1	59	97					5
Variable 1	117	80	139	239	185	180	173	175
Variable 2		418	125	283	62	117	245
Variable 2								12
Variable 2							176	315

<colgroup><col><col><col><col span="6"></colgroup><tbody>
</tbody>

 

[RasGhul]

Hi Gamingguy,

Is this what you need?

Book1
	A	B	C	D	E	F	G	H	I
1		ACTUAL	ACTUAL	ACTUAL	ACTUAL	ACTUAL	ACTUAL	ACTUAL	ACTUAL
2		2017	2017	2017	2017	2018	2018	2018	2018
3		2017.Q1	2017.Q2	2017.Q3	2017.Q4	2018.Q1	2018.Q2	2018.Q3	2018.Q4
4	Variable 1
5	Variable 1	59	97					5
6	Variable 1	117	80	139	239	185	180	173	175
7	Variable 2		418	125	283	62	117	245
8	Variable 2								12
9	Variable 2							176	315
10
11
12		2017	2018
13	Variable 1	731	718
14	Variable 2	826	927
Sheet1

Cell Formulas
Range	Formula
B13	=SUMPRODUCT(($A$4:$A$9=$A13)*($B$2:$I$2=B$12)*$B$4:$I$9)
C13	=SUMPRODUCT(($A$4:$A$9=$A13)*($B$2:$I$2=C$12)*$B$4:$I$9)