Multiple tabulation

[ExcelOrama]

Howdy...Looking for tips to achieve multiple sums.

I have a list of criteria along with scores assigned to each criterion, which will be used in drop-down lists in another sheet.

Txt 1 for criterion 1 Text 2 for criterion 1 Txt 3 for criterion 1 <colgroup><col width="121"></colgroup><tbody> </tbody> Txt 1 for criterion 2 <colgroup><col width="121"></colgroup><tbody> </tbody> Txt 2 for criterion 2 <colgroup><col width="121"></colgroup><tbody> </tbody> Txt 3 for criterion 2 <colgroup><col width="121"></colgroup><tbody> </tbody> 1 2 3 1 2 3 <tbody> </tbody>
When users select each of the criterion in a separate sheet as provided by drop-down lists, I need to tally up the values as affiliated with each criterion. For instance, when user selects Txt 3 for criterion 1 along with Txt 2 for criterion 2 from the drop-downs, total value should add up to 5. Any helpful suggestions are appreciated. List of Criteria Total Value Txt 3 for criterion 1 Txt 2 for criterion 2 5 <tbody> </tbody>

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

 

[Marcelo Branco]

Welcome to Mr Excel

Assuming the list of criteria is in Sheet1 A1:F2

Try

Sheet2

	A​	B​
1​	List of Criteria​	Total Value​
2​	Txt 3 for criterion 1​
3​	Txt 2 for criterion 2​	5​

Formula in B3
=SUMPRODUCT(SUMIF(Sheet1!$A$1:$F$1,A2:A3,Sheet1!$A$2:$F$2))

Hope this helps

M.

 

[ExcelOrama]

Thanks for the reply...I'm afraid that didn't produce any results, even after applying the array function.

 

[Marcelo Branco]

As i said above, i assumed that the criteria are in Sheet1 in the range A1:F2. If this is not correct, the formula will not work.
You need to tell us the exact location of the criteria - worksheet name, rows and columns - and also on which cells are the drop-downs on the other worksheet.

M.

 

[ExcelOrama]

Sheet 1
This is where the list of criteria with associated values reside.

	A	B	C	D	E	F
1	Txt 1 for criterion 1	Text 2 for criterion 1	Txt 3 for criterion 1	Txt 1 for criterion 2	Txt 2 for criterion 2	Txt 3 for criterion 2
2	1	2	3	1	2	3

<tbody>
</tbody>

Sheet 2
This sheet will use the values from sheet 1, which means users can select items dynamically. I lieu of drop-downs below, I've used examples to illustrate this. In these particular selections, total value should display 5.

	A	B
1	List of Criteria	Total Value
2	Text 2 for criterion 1 (user has selected items in Sheet1!B2)<dropdown list="" with="" values="" populated="" from="" sheet1!a1:c3=""></dropdown>
3	Txt 3 for criterion 2 (user has selected items in Sheet1!F2)<dropdown list="" with="" values="" populated="" from="" sheet1!d1:f3=""></dropdown>	5

<tbody>
</tbody>

Hope this is clear.

 

[Marcelo Branco]

The formula i suggested in post 2 should have worked. Repeating:
=SUMPRODUCT(SUMIF(Sheet1!$A$1:$F$1,A2:A3,Sheet1!$A$2:$F$2))

What happened:error, wrong result?

What exactly you meant by: (user has selected items in Sheet1!B2)

M.

 

[ExcelOrama]

Yes, it did work. I had referenced the cell range incorrectly. Thank you