SumIf

[capitolideas]

Hi,

I am trying to find a formula that will add up amounts only if a column on the left has a value of 100, 101, or 110. The data is unsorted and would prefer not to sort.


Example:

Column A Column B
199 9
101 1
120 2
100 1
100 4
110 10

Total = 16

Would I use match index? Sum if?

 

[barry houdini]

Try this formula

=SUM(SUMIF(A:A,{100,101,110},B:B))

I make the answer 16

 

[capitolideas]

bingo! Thank you!!!!

 

[capitolideas]

Ok. Part ll of this question.

What if I want to exclude certain numbers?

=SUM(SUMIF(E:E,{100,101,110},M:M)) - What's the version of this formula that would exclude 100, 101, & 110?

 

[capitolideas]

Any idea how to restructure this formula to exclude numbers that shouldn't be part of the sum?

 

[Fazza]

Add all values and then subtract the formula you have

 

[barry houdini]

If it's just 3 values to exclude you could do this:

=SUMIFS(M:M,E:E,"<>100",E:E,"<>101",E:E,"<>110")