Using sumproduct but ignoring duplicates

Geoff_canada

Board Regular
Joined
May 26, 2008
Messages
56
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 AColumn B
AB1223 11/30/2013
AB321212/05/2013
AB121212/05/2013
AB121212/05/2013
AB121212/05/2013
AB039312/16/2013
AB039312/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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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.
 

Geoff_canada

Board Regular
Joined
May 26, 2008
Messages
56
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

Board Regular
Joined
May 26, 2008
Messages
56
nevermind...figured it out...just chalk it up to still being early and me being stupid! Thx
 

Watch MrExcel Video

Forum statistics

Threads
1,123,156
Messages
5,600,049
Members
414,357
Latest member
Gemma_R

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top