Using sumproduct but ignoring duplicates

Board Regular
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

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Jerry Sullivan

MrExcel MVP
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.

Code:
``````=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.

Board Regular
Jerry

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

Board Regular
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?

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,168,037
Messages
5,856,962
Members
431,841
Latest member
jaybeem

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back