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.

[TABLE="width: 1106"]
<colgroup><col><col><col span="7"></colgroup><tbody>[TR]
[TD="colspan: 3"][TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]Txt 1 for criterion 1
[/TD]
[TD]Text 2 for criterion 1
[/TD]
[TD][TABLE="width: 121"]
<colgroup><col width="121"></colgroup><tbody>[TR]
[TD="class: xl65, width: 121"]Txt 3 for criterion 1
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 121"]
<colgroup><col width="121"></colgroup><tbody>[TR]
[TD="class: xl65, width: 121"]Txt 1 for criterion 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 121"]
<colgroup><col width="121"></colgroup><tbody>[TR]
[TD="class: xl65, width: 121"]Txt 2 for criterion 2
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 121"]
<colgroup><col width="121"></colgroup><tbody>[TR]
[TD="class: xl65, width: 121"]Txt 3 for criterion 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[/TR]
</tbody>[/TABLE]


[/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 3"][/TD]
[/TR]
[TR]
[TD]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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]List of Criteria
[/TD]
[TD]Total Value
[/TD]
[/TR]
[TR]
[TD]Txt 3 for criterion 1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Txt 2 for criterion 2
[/TD]
[TD]5
[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Welcome to Mr Excel

Assuming the list of criteria is in Sheet1 A1:F2

Try

Sheet2

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

List of Criteria​

[/td][td]

Total Value​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Txt 3 for criterion 1​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Txt 2 for criterion 2​

[/td][td]

5​

[/td][/tr]
[/table]

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.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Txt 1 for criterion 1[/TD]
[TD]Text 2 for criterion 1[/TD]
[TD]Txt 3 for criterion 1[/TD]
[TD]Txt 1 for criterion 2[/TD]
[TD]Txt 2 for criterion 2[/TD]
[TD]Txt 3 for criterion 2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]







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.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]List of Criteria[/TD]
[TD]Total Value[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Text 2 for criterion 1 (user has selected items in Sheet1!B2)<dropdown list="" with="" values="" populated="" from="" sheet1!a1:c3=""></dropdown>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Txt 3 for criterion 2 (user has selected items in Sheet1!F2)<dropdown list="" with="" values="" populated="" from="" sheet1!d1:f3=""></dropdown>
[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]







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