Trouble calculating from a column for a set of criteria from another column

[kristinerna]

I am trying to find a function that calculates a total from a column (D) corresponding to set of criteria (more than two) from another column (A). F.ex. I need the total for keys (col. A) 1100, 1110, 131001-131012 (total -309.000) without adding any of the values in between. Or for just 1110 and 131001-131012.

I have tried to use a few formulas, none of whom work;


=SUM(LOOKUP(1000; A1:A260; D1:D300); LOOKUP(1010; A1:A300; D1:D300))
This adds these two values correctly but doesn’t work with ranges.


=SUMPRODUCT(--(A5:A260>=1100); --(A5:A260<=1110); --(D5:D260))
This one works when done for one range, but when I try to add more than one range:


=SUMPRODUCT(--(A5:A260>=1100); --(A5:A260<=1110); --(D5:D260); --(A5:A260>=131001); --(A5:A260<=131012); --(D5:D260))
OR:
=SUMPRODUCT(--(A5:A260>=1100); --(A5:A260<=1110); --(A5:A260>=131001); --(A5:A260<=131012); --(D5:D260))
These yield 0.
I have also tried using these in combination with =SUM(… and =SUMIF/S(… but either it doesn’t work or I am doing something wrong.
Below is an example of the table the fits the criteria above (I am working from a larger table)

I am running 32-bit Excel from Microsoft Office Professional Plus 2010 on Windows 7.


I would really appreciate all the help I can get on this.
Kristin

 

[Andrew Poulsom]

Welcome to MrExcel.

Try:

=SUMPRODUCT(((A5:A260=1100)+(A5:A260=1110)+((A5:A260>=131001)*(A5:A260<=131012))),D5:D260)

 

[kristinerna]

Thank you!
I've tested your formula on every variation I can think of and it works every time.
I am so glad i found this page.

Gratefully,
Kristin