# SUMIF range from rows, but sum_range from columns

#### hakim_tanzil

##### Board Regular
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.

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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
ABCDEF
1NameA1A2A3A4A5
2Arron15322
3Bravo63213
4Aaron35673
5Charlie22343
6Bravo53432
Sheet2
<
Summary:

Book1
ABCDEF
10_A1A2A3A4A5
11Aaron35673
12Bravo116645
13Charlie22343
Sheet2

Enter this formula in B11:

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

Copy across to F11 then down as needed.

Copy over your range on Calculation sheet

Excel 2010
ABCDEF
1A1A2A3A4A5
2Aaron410995
3Bravo116645
4Charlie22343
Calculations
Cell Formulas
RangeFormula
B2=SUMPRODUCT((Input!\$A\$2:\$A\$6=Calculations!\$A2)*(Input!\$B\$1:\$F\$1=Calculations!B\$1)*(Input!\$B\$2:\$F\$6))

Or with an pivot table.

Gegevens = data

som van = sum of

Eindtotaal = Endtotal

FGHIJK
11Gegevens
12NameSom van A1Som van A2Som van A3Som van A4Som van A5
13Aaron35673
14Arron15322
15Bravo116645
16Charlie22343
17Eindtotaal1718181713

</TBODY>

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

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

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

Replies
2
Views
111
Replies
3
Views
147
Replies
17
Views
391
Replies
16
Views
312
Replies
2
Views
97

1,203,236
Messages
6,054,301
Members
444,715
Latest member
GlitchHawk

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back