Using sumproduct but ignoring duplicates

[Geoff_canada]

Hi everyone

Back again for some excel help. I have a requirement to perform a sum of data based on a date range, then counting the number of unique values in a separate columns. The data is a follows

Column A	Column B
AB1223	11/30/2013
AB3212	12/05/2013
AB1212	12/05/2013
AB1212	12/05/2013
AB1212	12/05/2013
AB0393	12/16/2013
AB0393	12/16/2013

<tbody>
</tbody>

I need to count up the unique values in column A based on column B equalling a date range. For example, if date range is between Dec 1 to 31, 2013 then the expected result would be 3.

Any help is greatly appreciated.

Thanks

 

[Jerry Sullivan]

Hi Geoff,

Try using the technique explained by Aladin in post #32 of this thread
http://www.mrexcel.com/forum/excel-...using-sum-if-frequency-match.html#post3156949

For your scenario, you can use the formula shown below after defining named ranges for the parts:
MyItems (values in column A)
MyDates (date values in column B)
DateFrom (cell that holds the start date eg 12/1/2013)
DateTo (cell that holds the end date eg 12/31/2013)

Array formulas need to be entered using a combination of Ctrl, Shift, Enter (not just Enter).
So, after pasting this formula with your cursor still in that Cell, hold down both the Ctrl key and the Shift key, then hit Enter.

=SUM(IF(FREQUENCY(
    IF(MyDates>=DateFrom,
        IF(MyDates<=DateTo,
            MATCH("~"&MyItems,MyItems&"",0))),
                 ROW(MyItems)-ROW(INDEX(MyItems,1,1)) +1),1))

If you prefer not to use named ranges, just substitute range addresses into the formula.

 

[Geoff_canada]

Jerry

Thanks for the information....your solution works for me...thanks again!

 

[Geoff_canada]

Jerry

Actually, for some reason I can get this to work if everything is on same worksheet. I used range addresses for the data in columns A and B etc....but if my source data is on one sheet...and the formula is on another it returns value of zero....

Your formula should work regardless no?

 

[Geoff_canada]

nevermind...figured it out...just chalk it up to still being early and me being stupid! Thx