1

How do I find the sum of Column Values for all values in Items . I used sumproduct but it has N/A error and couldn't find right solution with sumifs. =SUMPRODUCT(--(A2:A5=Sheet6!A2:A7),Sheet6!B2:B7) .Also in sumproduct I am unable to use range A:A

Items
a
b
c
d

.

Items   Values
a   1
b   2
c   3
a   4
b   5
c   6

The result should be 

a 5
b 7

With Additional Date Column The Date should be between Date1 and date2:

Items   date1   date2
a   06-01-13    06-02-13
b   07-01-13    07-02-13
c   08-01-13    08-02-13
d   09-01-13    09-02-13


Items   Values  Date
a   1   01-01-13
b   2   02-01-13
c   3   03-01-13
a   4   19-01-13
b   5   20-01-13
c   6   21-01-13
a   7   22-01-13

=SUMPRODUCT((A2=Sheet13!A2:A8)*AND(B2<=Sheet13!C2:C8,Sheet12!C2>=Sheet13!C2:C8)*Sheet13!B2:B8)

kinkajou
  • 3,664
  • 25
  • 75
  • 128

1 Answers1

1

Try this (This goes in Cell B2)

=SUMPRODUCT((Sheet6!$A$2:$A$7=A2)*(Sheet6!$B$2:$B$7))

Change $7 to what ever the last row has the data.

EDIT

Screenshot

enter image description here

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • It works but as I add date column and compare it's not returning result (result is always 0). Is my formula I added wrong? – kinkajou Apr 16 '13 at 10:20
  • 1
    Try this `=SUMPRODUCT((Sheet13!$A$2:$A$5=A2)*(C2>=Sheet13!$B$2:$B$5)*(C2<=Sheet13!$C$2:$C$5))` in cell `D2` – Siddharth Rout Apr 16 '13 at 10:41
  • Wouldn't SUMIF be the easiest solution for the first query? In Excel 2007 you can also use COUNTIFS for multi-conditional counting, e.g. `=COUNTIFS(Sheet13!$A:$A,A2,Sheet13!$B:$B,"<="&C2,Sheet13!$C:$C,">="&C2)` or change to SUMIFS if you want a sum based on multiple conditions – barry houdini Apr 16 '13 at 11:36