SUMIF range from rows, but sum_range from columns

[hakim_tanzil]

Hi all,

Assuming my input sheet is:

Name A1 A2 A3 A4 A5
Arron 1 5 3 2 2
Bravo 6 3 2 1 3
Aaron 3 5 6 7 3
Charlie 2 2 3 4 3
Bravo 5 3 4 3 2

In my calculation sheet:

A1 A2 A3 A4 A5
Aaron
Bravo
Charlie

So, I want to use the SUMIF formula in my calculation sheet (cell B2:F3) to sum up everything from input sheet, based on names and the product name (A1,A2,A3, etc). Thanks and look forward to your inputs on this.

 

[T. Valko]

Hi all,

Assuming my input sheet is:

Name A1 A2 A3 A4 A5
Arron 1 5 3 2 2
Bravo 6 3 2 1 3
Aaron 3 5 6 7 3
Charlie 2 2 3 4 3
Bravo 5 3 4 3 2

In my calculation sheet:

A1 A2 A3 A4 A5
Aaron
Bravo
Charlie

So, I want to use the SUMIF formula in my calculation sheet (cell B2:F3) to sum up everything from input sheet, based on names and the product name (A1,A2,A3, etc). Thanks and look forward to your inputs on this.

Try this...

Data:

Book1
	A	B	C	D	E	F
1	Name	A1	A2	A3	A4	A5
2	Arron	1	5	3	2	2
3	Bravo	6	3	2	1	3
4	Aaron	3	5	6	7	3
5	Charlie	2	2	3	4	3
6	Bravo	5	3	4	3	2
Sheet2

<
Summary:

Book1
	A	B	C	D	E	F
10	_	A1	A2	A3	A4	A5
11	Aaron	3	5	6	7	3
12	Bravo	11	6	6	4	5
13	Charlie	2	2	3	4	3
Sheet2

Enter this formula in B11:

=SUMIF($A$2:$A$6,$A11,B$2:B$6)

Copy across to F11 then down as needed.

 

[TheCman81]

Copy over your range on Calculation sheet

Excel 2010
	A	B	C	D	E	F
1		A1	A2	A3	A4	A5
2	Aaron	4	10	9	9	5
3	Bravo	11	6	6	4	5
4	Charlie	2	2	3	4	3
Calculations

Cell Formulas
Range	Formula
B2	=SUMPRODUCT((Input!$A$2:$A$6=Calculations!$A2)*(Input!$B$1:$F$1=Calculations!B$1)*(Input!$B$2:$F$6))

 

[Oeldere]

Or with an pivot table.

Gegevens = data

som van = sum of

Eindtotaal = Endtotal

Blad4

	F	G	H	I	J	K
11		Gegevens
12	Name	Som van A1	Som van A2	Som van A3	Som van A4	Som van A5
13	Aaron	3	5	6	7	3
14	Arron	1	5	3	2	2
15	Bravo	11	6	6	4	5
16	Charlie	2	2	3	4	3
17	Eindtotaal	17	18	18	17	13

<THEAD>
</THEAD><TBODY>
</TBODY>

 

[PCL]

In calculation sheet
B2 =SUMPRODUCT((Input!$A$2:$A$6=$A2)*(Input!B$2:B$6))
Copy and drag where you need

 

[hakim_tanzil]

Hello guys, thanks all for the inputs and sorry for the late response to your reply. I tried inputs from TheCman81 and from PCL, unfortunately, both didn't work. So how exactly is formula "SUMPRODUCT" works? I tried formula from TheCman81 and modified a bit:

=SUMPRODUCT((Input!A:A=Calculations!$A2)*(Input!1:1=Calculations!B$1)*(Input!B:F))

I deleted the cell number since the data inputs in "INPUT" sheet could have around 4000 rows of inputs

 

[PCL]

Never use the full column and / or full row with SUMPRODUCT
be explicite concerning the range used:e.g. A1:A100