• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Frequency function

Busymanjohn

Busymanjohn

Member
Hi all, I am using the following array formula =SUM(IF(FREQUENCY(IF($B$7:$B$500=B7,IF($I$7:$I$500=M$6,MATCH($D$7:$D$500,$D$7:$D$500,0))),ROW($B$7:$B$500)-ROW($C$7)+1)>0,$C$7:$C$500))

it works fine and returns the right info, but I would like to add one more criteria ,,, for data in Col H ... so an IF(H$7:H$500=L7 ..... but I get an error when I try to put it into the formula, any ideas?
 
Hi,

If you would post a sample file with some data and required output, it will be helpful for readers who wants to help you.

Regards,
 
Sorry with this it's hard to work as, this is not giving information about row and column number + whats in B7,M$6, L7....

If you can upload a file with dummy data by removing confidential data, that would also work.

Regards,
 
Hi, B7 contains a part number ,,,, 123456abc and M6 contains the heading Shipped, the formula currently returns the correct information in that for 123456abc that have shipped and looks at Frequency of BR DO No. returns the value of 4,571 ( the qty of that part number that have shipped and not double counting the BR DO No. ) .... now I want to add in another criteria, I want to know how many of that 4,571 shipped on 01/08/14, how many shipped on 02/08/14 etc etc to the total of 4,571. Make sense?
 
Hi,

Try below array formula:

Code: 
=SUM(IF(FREQUENCY(IFERROR(MATCH(IF(($B$7:$B$23=B7)*($I$7:$I$23=M$6)*($H$7:$H$23=L7),$D$7:$D$23),$D$7:$D$23,0),"e"),ROW($B$7:$B$23)-ROW($B$7)+1),$C$7:$C$23))

in M7 and copy down.

Regards,
 
Works to a certain extent, although it returns a value of 3,991 on 05/08/14, when in fact that qty actually shipped on 04/08/14, snapshot attached to show what I mean, your formula results are on the right ,,,, the left hand side is what it should look like ...

upload_2014-8-7_7-28-27.png
 
Hi, They actually shipped on 04/08/2014, same BR DO No., so the formula should not count them as the BR DO No. appears as shipped on 04/08/2014, they show up on the report I receive as it is a data dump from a system that show all data either shipped or not, it's the BR DO No. that is unique in each case. So, i only want to count the ships for that BR DO No. once
 
Yeah, wasn't even for me, was for a senior manager, let's see how often it gets used and if they decide to make changes ..... but they have what they asked for, I was on the right track, just couldn't figure out the last piece of the puzzle ,,, which is why I love this site, so many helpful folk. Thanks again
 
You must log in or register to reply here.
Back
Top