• 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.

SUMPRODUCT

Sir Hames

New Member
Hi Guys I'm a little stumped and I need some help. I have three columns (Column A -OPTION YR, Column B - Purchase Order, Column C - PO CLOSED). For this particular spreadsheet I need to uniquely identify Purchase Orders were CLOSED in OPTION YR 2. But I have Purchase Orders that have the same number and do not need them counted twice.......anyone know how to get the value based on the sumproduct formula??? Below the answer I'm trying to get is 3

OPTION YR PURCHASE ORDER PO CLOSED
2 N589 Y
2 N589 Y
2 N456 Y
2 N093 Y
1 T123 Y
1 T145 Y
1 T156 Y
5 M834 Y
 
Sir Hames

Firstly, Welcome to the Chandoo.org Forums

Try: =SUM(IF(FREQUENCY(MATCH(B2:B9,B2:B9,0)*(A2:A9=2), MATCH(B2:B9,B2:B9,0))>0, 1)) Ctrl+Shift+Enter
 
Hi,

See the file for result in yellow cell. Note the formula is an array formula, so must be entered with Ctrl+Shift+Enter.

Regards,
 

Attachments

  • UniqueCountText.xlsx
    11.1 KB · Views: 6
Hey guys thanks for the quick reply, would this same formula work if there are blanks in column C "PO CLOSED"
 
Hi,

In the file if you see the formula is driven by PO CLOSED, so if there is a blank it will not count it.

Do want to count that PO or NOT?

If you don't want to count PO if it is blank than use the formula in file else use below formula:

=SUMPRODUCT(IF(FREQUENCY(IFERROR(MATCH(IF($A$2:$A$9=2,$B$2:$B$9),$B$2:$B$9,0),"e"),ROW($B$2:$B$9)-ROW($B$2)+1),1,0))

Enter with Ctrl+Shift+Enter.

Regards,
 
Back
Top