How to sum up a specific colum from a range of columns using different criterias

[Spiru]

Hello,

I have the below two sheets Sheet 1 and Sheet 2 ( the one with the VALUE error) and in Sheet2 I'm trying to obtain the sum of the totals from Sheet1 using a formula with multiple criteria. E.g. I want to see in Sheet2 the results of all the Vichy, Xerolys and Home but only for a specific column: If in Sheet2 in cell A1 I have number 5 then I want the formula to add up the totals from the column corresponding to 5 in Sheet1 ( 87, 14, 26). The formula that I've tried to use is #=SUMIFS(Sheet1!$B$5:$F$7,Sheet1!$A$5:$A$7,Sheet2!A4,Sheet1!$B$3:$F$3,Sheet2!$A$1)# but I receive a VALUE error. Can anyone help?

Thank you,

Sheet1

[TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64, align: right"]2[/TD]
[TD="width: 64, align: right"]3[/TD]
[TD="width: 64, align: right"]4[/TD]
[TD="width: 64, align: right"]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Vichy [/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]87[/TD]
[/TR]
[TR]
[TD]Xerolys [/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]899[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]Home[/TD]
[TD="align: right"]1345[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]565[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]26[/TD]
[/TR]
</tbody>[/TABLE]


and

Sheet2

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]5[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]Vichy [/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]Xerolys [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Home[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

In B3 of Sheet2 enter and copy down:

=SUMIFS(INDEX(Sheet1!B:F,0,MATCH($A$1,INDEX(Sheet1!B:F,1,0),0),Sheet1!A:A,$A3)

where A1 = 5 and A3 = Vichy.

 

[Spiru]

Hi Aladin,

Thanks for your replay. I've entered the formula, but It says that I've entered too many arguments for this function

=sumifs(index(Sheet1!B:F,0,match(Sheet2!$A$1,Index(Sheet1!B:F,3,0),0),Sheet1!A:A,Sheet2!A4)

where A1 = 5 and A4 = Vichy

 

[Spiru]

thanks a lot! It works it was missing a parenthesis

=SUMIFS(INDEX(Sheet1!B:F,0,MATCH(Sheet2!$A$1,INDEX(Sheet1!B:F,3,0),0)),Sheet1!A:A,Sheet2!A4)

 

[Aladin Akyurek]

thanks a lot! It works it was missing a parenthesis

=SUMIFS(INDEX(Sheet1!B:F,0,MATCH(Sheet2!$A$1,INDEX(Sheet1!B:F,3,0),0)),Sheet1!A:A,Sheet2!A4)

You are welcome.

 

[Mike Szczesny]

Can you use something like this. Hope this helps.

Excel 2012
	A	B	C	D	E	F
1		a	b	c	d	e
2	Vichy	45.00	43.00	56.00	65.00	87.00
3	Xerolys	23.00	43.00	65.00	899.00	14.00
4	Home	50.00	65.00	565.00	76.00	26.00
5
6	e
7
8		127
Sheet1

Cell Formulas
Range	Formula
B8	{=SUM(IF($B$1:$F$1=$A$6,$B$2:$F$4))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Spiru]

Can you use something like this. Hope this helps.

Excel 2012

	A	B	C	D	E	F
a	b	c	d	e
Vichy
Xerolys
Home
e

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]45.00[/TD]
[TD="align: right"]43.00[/TD]
[TD="align: right"]56.00[/TD]
[TD="align: right"]65.00[/TD]
[TD="align: right"]87.00[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]23.00[/TD]
[TD="align: right"]43.00[/TD]
[TD="align: right"]65.00[/TD]
[TD="align: right"]899.00[/TD]
[TD="align: right"]14.00[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]50.00[/TD]
[TD="align: right"]65.00[/TD]
[TD="align: right"]565.00[/TD]
[TD="align: right"]76.00[/TD]
[TD="align: right"]26.00[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]127[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B8[/TH]
[TD="align: left"]{=SUM(IF($B$1:$F$1=$A$6,$B$2:$F$4))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Hi Mike,

Sorry for the late replay and thanks for replaying to my question. Your formula sums up all the amounts for the column from B1 to F1 that matches cell A6 for Vichy Xerolys and Home and I needed to sum up the amounts just for all the Vichy or all Home or all Xerolys within that column. So I needed to search based on two criterias : 1. that the column from B1 to F1 matches cell A6 and 2. that the row from A2 to A4 matches Vichy or that it matches Xerolys or Home. Aladin's formula works .

Thanks again!

 

[Aladin Akyurek]

Whenever a range-processing formula (ones with SUMIFS and kindred functions) applies and is appropriate, an array-processing formula should be avoided.