Odd result from a formula that counts number of unique items

[shavar]

Hello everyone. First post here.


I learned how to write a formula from this forum a while back and I use it in several places in a spreadsheet. I've recently started a new data set for 2013 and I noticed something odd.


I have several ranges that I reference in it. Basically what it's supposed to do is give me the number of unique items (ItemNumber) that were received (DateReceived) in Jan of 2013 for location West (Location). This formula looks at a Month column with names in the format of Jan, Feb, Mar thus the --(TEXT(DateReceived,"Mmm")=$A89). Looking at the table here I should get 4. But what I'm getting instead is 3.25 or 3 if I decrease the decimal. I don't follow why this is happening. I should always get a whole number shouldn't I? I am using the value of this formula for other calulations so it is causing some issues with that. I evaluated the formula and it seems to be working. At least I'm not getting any errors.


Hoping someone can provide some insight to this.


=SUMPRODUCT(--(DateReceived<>""),--(TEXT(DateReceived,"Mmm")=$A89),--(YEAR(DateReceived)=2013)

,--(Location="West")/COUNTIF(ItemNumber,ItemNumber&""))


Item# Date Received Location

9729 12/5/2012 West

9938 12/13/2012 West

9939 12/13/2012 West

8354 1/9/2013 West

0315 1/14/2013 West

0316 1/14/2013 West

0372 1/15/2013 West

 

[NARAYANK991]

Hi Shavar ,


Try changing your formula to :


=SUMPRODUCT((--(DateReceived<>"")*--(TEXT(DateReceived,"Mmm")=$A89)*--(YEAR(DateReceived)=2013)

*--(Location="West"))/COUNTIF(ItemNumber,ItemNumber&""))


Narayan

 

[Debraj]

Hi Shavar..


Welcome to the forum..


Its god to know that you have learned a lot.. But I think you are missing something..

To count Unique.. You need to add all criteria used in Sumproduct part, in Countifs also..


So that.. (.33,.33,.33) total up to 1.


Please check if the below is working as per requirement..

=SUM(IF((Location="West")*(TEXT(DateReceived,"MMMYY")=D1&"13")*(DateReceived<>""),1/COUNTIFS(Location,"West",DateReceived,">"&DATEVALUE("01-"&D1&"-2013"),DateReceived,"<"&DATEVALUE("01-"&TEXT((D1&1)+31,"mmm")&"13")-1,DateReceived,"<>"&"",Item,Item&"")))

with Ctrl+Shift+Enter


@ Narayan: Can you please help in COUNTIFS part to check date between.. In case of Month as TEXT..


Regards,

Deb

 

[Debraj]

OOPS.. Shavar.. Please change D1 to $A89..

 

[NARAYANK991]

Hi Deb ,


Your formula works correctly.


@Shavar ,


Please disregard my earlier post ; I had not gone through your post to understand your requirement.


Narayan

 

[shavar]

Thank you Deb and Narayan for your time.


@Deb your array formula indeed works. I will need to go through and edit some of my other formulas based and what you've shown me here. And I'll need to dig in to really understand what's going on with the formula.


I will end up with about 5,000 records by the end of the year and this formula or something similar will be calculation similar data and a dozen or so different cells. Should I be concerned about performance and calculation time with a bunch of these formulas in the workbook?


-Shane

 

[bobhc]

Good day


Posts edited to fit post area and do away with scroll bar

 

[SirJB7]

Hi, shavar!

Despite of future (I didn't say late, only future...) Debraj Roy's answer, I'm afraid that 5K array formulas would take a little to calculate, so as you guessed you should be concerned about calculation time.

Regards!


@Debraj Roy

Hi!

I dared to share an early comment with the OP, hope you don't mind, do you?

Regards!


@b(ut)ob(ut)hc

Hi!

Have you seen my improved vocabulary?

Regards!