SumIF with multiple lists

[Jammo]

Hi all,
I've been looking around but cant find an answer to this (seemingly) simple question. (Apologies if the text doesnt layout correctly)

Say I have a 2-column list of values - text and its assigned number:

text1 100
text2 200
text3 500
text4 1000
...
text8 200
text9 50

In another sheet I will have a single column with some of the elements of that original list, such as:

text2
text4
text9

I need a one-cell formula which looks up those values I've typed in, gets its appropriate number beside it, and then sums it. So in the above example, I need the value "1250" in one cell.

I'm playing around with SUMIF and array functions, but can't nail it.

I would like the formula to be boundless, if possible, by using "a:a" or "n:n", but if that doesn't work, I'm happy to limit it in the formula, such as "a1:1000" or "n1:n1000"

Any help would be greatly appreciated.

Many thanks,
Jammo

 

[Richard Schollar]

Hi Jammo & Welcome to the Board!

Try:

Sheet3<table style="border: 1.5px solid rgb(166, 170, 182); font-family: Calibri,Arial; font-size: 11pt; background-color: rgb(255, 255, 255); border-collapse: collapse; color: rgb(38, 34, 48);" border="1" cellpadding="0" cellspacing="0"><colgroup><col style="background-color: rgb(224, 224, 240);"><col><col><col><col><col></colgroup><tbody><tr style="border: 1px solid rgb(166, 170, 182); background-color: rgb(224, 224, 240); text-align: center; color: rgb(22, 17, 32); padding-left: 0.3em; padding-right: 0.3em;"><td style="border: 1px solid rgb(166, 170, 182); padding-left: 0.3em; padding-right: 0.3em;">
</td><td style="border: 1px solid rgb(166, 170, 182); padding-left: 0.3em; padding-right: 0.3em;">A</td><td style="border: 1px solid rgb(166, 170, 182); padding-left: 0.3em; padding-right: 0.3em;">B</td><td style="border: 1px solid rgb(166, 170, 182); padding-left: 0.3em; padding-right: 0.3em;">C</td><td style="border: 1px solid rgb(166, 170, 182); padding-left: 0.3em; padding-right: 0.3em;">D</td><td style="border: 1px solid rgb(166, 170, 182); padding-left: 0.3em; padding-right: 0.3em;">E</td></tr><tr><td style="border: 1px solid rgb(166, 170, 182); color: rgb(22, 17, 32); padding-left: 0.3em; padding-right: 0.3em;">1</td><td style="color: rgb(0, 0, 0); padding-left: 0.3em; padding-right: 0.3em;">text1</td><td style="color: rgb(0, 0, 0); padding-left: 0.3em; padding-right: 0.3em;">100</td><td style="color: rgb(0, 0, 0); padding-left: 0.3em; padding-right: 0.3em;">
</td><td style="color: rgb(0, 0, 0); padding-left: 0.3em; padding-right: 0.3em;">
</td><td style="color: rgb(0, 0, 0); padding-left: 0.3em; padding-right: 0.3em;">text2</td></tr><tr><td style="border: 1px solid rgb(166, 170, 182); color: rgb(22, 17, 32); padding-left: 0.3em; padding-right: 0.3em;">2</td><td style="color: rgb(0, 0, 0); padding-left: 0.3em; padding-right: 0.3em;">text2</td><td style="color: rgb(0, 0, 0); padding-left: 0.3em; padding-right: 0.3em;">200</td><td style="color: rgb(0, 0, 0); padding-left: 0.3em; padding-right: 0.3em;">
</td><td style="color: rgb(0, 0, 0); padding-left: 0.3em; padding-right: 0.3em;">
</td><td style="color: rgb(0, 0, 0); padding-left: 0.3em; padding-right: 0.3em;">text4</td></tr><tr><td style="border: 1px solid rgb(166, 170, 182); color: rgb(22, 17, 32); padding-left: 0.3em; padding-right: 0.3em;">3</td><td style="color: rgb(0, 0, 0); padding-left: 0.3em; padding-right: 0.3em;">text3</td><td style="color: rgb(0, 0, 0); padding-left: 0.3em; padding-right: 0.3em;">500</td><td style="color: rgb(0, 0, 0); padding-left: 0.3em; padding-right: 0.3em;">
</td><td style="color: rgb(0, 0, 0); padding-left: 0.3em; padding-right: 0.3em;">
</td><td style="color: rgb(0, 0, 0); padding-left: 0.3em; padding-right: 0.3em;">text9</td></tr><tr><td style="border: 1px solid rgb(166, 170, 182); color: rgb(22, 17, 32); padding-left: 0.3em; padding-right: 0.3em;">4</td><td style="color: rgb(0, 0, 0); padding-left: 0.3em; padding-right: 0.3em;">text4</td><td style="color: rgb(0, 0, 0); padding-left: 0.3em; padding-right: 0.3em;">1000</td><td style="color: rgb(0, 0, 0); padding-left: 0.3em; padding-right: 0.3em;">
</td><td style="color: rgb(0, 0, 0); padding-left: 0.3em; padding-right: 0.3em;">
</td><td style="color: rgb(0, 0, 0); padding-left: 0.3em; padding-right: 0.3em;">
</td></tr><tr><td style="border: 1px solid rgb(166, 170, 182); color: rgb(22, 17, 32); padding-left: 0.3em; padding-right: 0.3em;">5</td><td style="color: rgb(0, 0, 0); padding-left: 0.3em; padding-right: 0.3em;">...</td><td style="color: rgb(0, 0, 0); padding-left: 0.3em; padding-right: 0.3em;">
</td><td style="color: rgb(0, 0, 0); padding-left: 0.3em; padding-right: 0.3em;">
</td><td style="color: rgb(0, 0, 0); padding-left: 0.3em; padding-right: 0.3em;">
</td><td style="color: rgb(0, 0, 0); padding-left: 0.3em; padding-right: 0.3em;">
</td></tr><tr><td style="border: 1px solid rgb(166, 170, 182); color: rgb(22, 17, 32); padding-left: 0.3em; padding-right: 0.3em;">6</td><td style="color: rgb(0, 0, 0); padding-left: 0.3em; padding-right: 0.3em;">text8</td><td style="color: rgb(0, 0, 0); padding-left: 0.3em; padding-right: 0.3em;">200</td><td style="color: rgb(0, 0, 0); padding-left: 0.3em; padding-right: 0.3em;">
</td><td style="color: rgb(0, 0, 0); padding-left: 0.3em; padding-right: 0.3em;">
</td><td style="color: rgb(0, 0, 0); padding-left: 0.3em; padding-right: 0.3em;">
</td></tr><tr><td style="border: 1px solid rgb(166, 170, 182); color: rgb(22, 17, 32); padding-left: 0.3em; padding-right: 0.3em;">7</td><td style="color: rgb(0, 0, 0); padding-left: 0.3em; padding-right: 0.3em;">text9</td><td style="color: rgb(0, 0, 0); padding-left: 0.3em; padding-right: 0.3em;">50</td><td style="color: rgb(0, 0, 0); padding-left: 0.3em; padding-right: 0.3em;">
</td><td style="color: rgb(0, 0, 0); padding-left: 0.3em; padding-right: 0.3em;">
</td><td style="color: rgb(0, 0, 0); padding-left: 0.3em; padding-right: 0.3em;">1250</td></tr></tbody></table>

<table><tbody><tr><td style="border: 2px solid rgb(0, 0, 9); padding: 0.3em; background-color: rgb(255, 255, 255);">Worksheet Formulas<table style="border: 1.5px solid rgb(166, 170, 182); background-color: rgb(255, 255, 255); border-collapse: collapse;" border="1" cellpadding="0" cellspacing="0"><tbody><tr><td style="border: 1px solid rgb(166, 170, 182); padding-left: 0.3em; padding-right: 0.3em; background-color: rgb(224, 224, 240);">Cell</td><td style="border: 1px solid rgb(166, 170, 182); padding-left: 0.3em; padding-right: 0.3em; background-color: rgb(224, 224, 240);">Formula</td></tr><tr><td style="font-weight: bold; padding-left: 0.3em; padding-right: 0.3em;">E7</td><td style="padding-left: 0.3em; padding-right: 0.3em;">=SUMPRODUCT(ISNUMBER(MATCH($A$1:$A$7,$E$1:$E$3,0))+0,$B$1:$B$7)</td></tr></tbody></table></td></tr></tbody></table>

You need specific range sizes for this unless you are using xl2007

 

[Aladin Akyurek]

Also, with SumIf...

=SUMPRODUCT(SUMIF(Sheet1!$A$2:$A$100,E2:E4,Sheet1$B$2:$B$100))

where Sheet1!A2:B100 houses the data to process and E2:E4 on the formula sheet the set of criteria for which a total must be calculated.

 

[Jammo]

Absolutely perfect. Worked a charm.

Thanks very much

My aviation log book is now fully functional.

All the best,
Jammo